MySQL Date/Time Types: DATE, DATETIME, TIMESTAMP, TIME, and YEAR

Dates and times are critical in applications such as:

• Tracking user activity
• Logging events
• Scheduling tasks
• Calculating durations or age

Using the wrong date/time type can lead to storage inefficiencies, incorrect calculations, or unexpected behavior, especially with time zones.

1. DATE: Storing Calendar Dates

The DATE type stores only the date (year, month, day) without any time component. The format is:

YYYY-MM-DD

Example:

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(50),
    event_date DATE
);

INSERT INTO events (id, event_name, event_date)
VALUES (1, 'Conference', '2026-06-15');

SELECT * FROM events;

Step-by-Step Explanation:

1. A table events is created with event_date as a DATE type.
2. We insert an event with the date 2026-06-15.
3. Only the calendar date is stored—no hours, minutes, or seconds.

Use Cases for DATE:

• Birthdays
• Holidays
• Project deadlines

2. DATETIME: Complete Date and Time

The DATETIME type stores both the date and time in the format:

YYYY-MM-DD HH:MM:SS

Example:

CREATE TABLE appointments (
    id INT PRIMARY KEY,
    client_name VARCHAR(50),
    appointment_time DATETIME
);

INSERT INTO appointments (id, client_name, appointment_time)
VALUES (1, 'Alice', '2026-06-15 14:30:00');

SELECT * FROM appointments;

Step-by-Step Explanation:

1. The appointment_time column is defined as DATETIME.
2. We insert both the date and time in a single value.
3. Queries return the exact timestamp, useful for scheduling and logs.

Key Feature:

• DATETIME does not automatically adjust for time zones; it stores exactly what you provide.

Use Cases for DATETIME:

• Booking systems
• Audit trails
• Event timestamps

3. TIMESTAMP: Date/Time with Time Zone Awareness

TIMESTAMP is similar to DATETIME but stores values in UTC internally and converts to the session’s time zone on retrieval. The format is the same as DATETIME:

YYYY-MM-DD HH:MM:SS

Example:

CREATE TABLE user_logins (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_logins (id, username)
VALUES (1, 'bob');

SELECT * FROM user_logins;

Step-by-Step Explanation:

1. login_time is defined as a TIMESTAMP with a default of CURRENT_TIMESTAMP.
2. When we insert a record without specifying login_time, MySQL automatically records the current UTC time.
3. The value adjusts based on the server or client session time zone on retrieval.

Use Cases for TIMESTAMP:

• Recording activity logs
• Tracking last modification times
• Coordinating events across multiple time zones

4. TIME: Storing Time of Day

The TIME type stores only the time of day (hours, minutes, seconds) or durations, in the format:

HH:MM:SS

Example:

CREATE TABLE shifts (
    id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    shift_start TIME,
    shift_end TIME
);

INSERT INTO shifts (id, employee_name, shift_start, shift_end)
VALUES (1, 'John', '09:00:00', '17:00:00');

SELECT * FROM shifts;

Step-by-Step Explanation:

1. shift_start and shift_end store only the time portion.
2. Useful for calculating durations: shift_end - shift_start.

Use Cases for TIME:

• Work shifts
• Cooking timers
• Event durations

5. YEAR: Storing Year Values

The YEAR type stores a single year in 4-digit format:

YYYY

Example:

CREATE TABLE movies (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR
);

INSERT INTO movies (id, title, release_year)
VALUES (1, 'Future Movie', 2026);

SELECT * FROM movies;

Step-by-Step Explanation:

1. The release_year column stores only the year.
2. Perfect for scenarios where only the year matters, without month or day.

Use Cases for YEAR:

• Movie release years
• Historical events
• Manufacturing dates

Quick Comparison of MySQL Date/Time Types

Best Practices for Using MySQL Date/Time Types

1. Choose the right type for your data – Avoid storing a full DATETIME if only a DATE is required.
2. Use TIMESTAMP for logging events – Automatic updates and time zone adjustments are very useful.
3. Store durations in TIME – It simplifies calculations like shift_end - shift_start.
4. Normalize your data – Avoid redundant storage by choosing types that precisely match your data requirements.