Date and Time data type refers to storing date or time-based data such as "2025-04-15", "12:30:00", etc.
When you create a table in MySQL, you define the date and time data type (like DATE, DATETIME, TIME, etc.) at that time so that only date or time-related values can be inserted into that column.
Syntax:
CREATE TABLE table_name (
column_name DATE,
column_name2 TIME,
column_name3 DATETIME
);
Example:
Suppose you want to make a table to keep employee attendance, including the date of attendance, check-in time, and the full timestamp. You can use the DATE, TIME, and DATETIME types like this:
Query:
CREATE TABLE employee_attendance ( employee_id INT, attendance_date DATE, -- stores only the date, e.g., 2025-04-15 check_in_time TIME, -- stores only the time, e.g., 09:30:00 record_timestamp DATETIME -- it keeps both date and time together in a single field, e.g., 2025-04-15 09:30:00 );
Insert Values into the Table:
INSERT INTO employee_attendance (employee_id, attendance_date, check_in_time, record_timestamp)
VALUES (101, '2025-04-15', '09:30:00', '2025-04-15 09:30:00');
Output:
employee_id | attendance_date | check_in_time | record_timestamp
----------------------------------------------------------------
101 | 2025-04-15 | 09:30:00 | 2025-04-15 09:30:00
Explanation:
attendance_date→ stores only the date.check_in_time→ stores only the time.record_timestamp→ stores both date and time together.