MySQL Date and Time Data Types

Almost every real-world database needs to record when something happened — when an order was placed, when a user was born, or when a post was last updated. MySQL provides five dedicated date and time types for this purpose. Each one stores a different combination of date, time, or both, and understanding the difference between them will help you avoid common mistakes like storing timestamps as plain text.

---

DATE — Calendar Dates

The DATE type stores a calendar date in the format YYYY-MM-DD. It holds no time information — just the year, month, and day.

• Format: YYYY-MM-DD
• Range: 1000-01-01 to 9999-12-31
• Storage: 3 bytes

CREATE TABLE employees (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO employees (full_name, date_of_birth, hire_date)
VALUES ('Rahim Uddin', '1990-06-15', '2021-03-01');

---

TIME — Time of Day

The TIME type stores a time value in the format HH:MM:SS. It can also represent elapsed time or time intervals, so it actually supports values outside the 24-hour clock range.

• Format: HH:MM:SS
• Range: -838:59:59 to 838:59:59
• Storage: 3 bytes

CREATE TABLE schedules (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    day_name VARCHAR(10) NOT NULL,
    open_time TIME NOT NULL,
    close_time TIME NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO schedules (day_name, open_time, close_time)
VALUES ('Monday', '09:00:00', '18:00:00');

---

DATETIME — Date and Time Together

DATETIME stores a full date and time value in the format YYYY-MM-DD HH:MM:SS. It is the most complete date-time type and is useful when you need to record exactly when something happened — down to the second.

• Format: YYYY-MM-DD HH:MM:SS
• Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
• Storage: 8 bytes
• Time zone aware: No — stores the value as-is

CREATE TABLE appointments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    patient_name VARCHAR(100) NOT NULL,
    appointment_at DATETIME NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO appointments (patient_name, appointment_at)
VALUES ('Nadia Islam', '2025-08-20 10:30:00');

---

TIMESTAMP — Auto-Recorded Date and Time

TIMESTAMP stores a date and time value just like DATETIME, but with two important differences: it has a narrower range, and it is time zone aware — MySQL converts it to UTC when storing and back to the session's time zone when retrieving.

• Format: YYYY-MM-DD HH:MM:SS
• Range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
• Storage: 4 bytes
• Time zone aware: Yes

The most common use of TIMESTAMP is to automatically record when a row was created or last updated:

CREATE TABLE blog_posts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

With this setup, created_at is set automatically when a row is inserted, and updated_at updates itself every time the row is modified — without any extra code in your application.

---

YEAR — Just the Year

The YEAR type stores a four-digit year value. It uses only 1 byte, making it the most compact date-related type in MySQL.

• Format: YYYY
• Range: 1901 to 2155
• Storage: 1 byte

graduation_year YEAR NOT NULL

---

Comparison: All Date and Time Types

Type	Stores	Format	Storage	Time Zone Aware
DATE	Date only	YYYY-MM-DD	3 bytes	✘ No
TIME	Time only	HH:MM:SS	3 bytes	✘ No
DATETIME	Date + time	YYYY-MM-DD HH:MM:SS	8 bytes	✘ No
TIMESTAMP	Date + time (UTC)	YYYY-MM-DD HH:MM:SS	4 bytes	✔ Yes
YEAR	Year only	YYYY	1 byte	✘ No

---

DATETIME vs TIMESTAMP — Which to Use?

This is one of the most common questions beginners ask. Here is a practical way to think about it:

• Use TIMESTAMP for created_at and updated_at columns that should auto-populate and are in the present era. It is time-zone aware and uses less storage.
• Use DATETIME when you need to store dates far in the past or far into the future, or when you want to store the date/time exactly as entered without any time zone conversion — for example, appointment times, event schedules, or historical records.

---

Practical Example

Here is an events table that brings several date and time types together:

CREATE TABLE events (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    event_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    scheduled_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

---

Key Points to Remember

• Use DATE for birthdays, deadlines, and event dates — when time is not needed.
• Use TIME for opening hours, durations, or any time-only value.
• Use DATETIME for appointments, schedules, and historical records that need exact date and time without time zone conversion.
• Use TIMESTAMP with DEFAULT CURRENT_TIMESTAMP for created_at and updated_at columns — MySQL fills them in automatically.
• TIMESTAMP is time-zone aware; DATETIME is not.
• TIMESTAMP only supports dates up to 2038 — use DATETIME for dates beyond that.
• Use YEAR for year-only values — it costs just 1 byte.