SQL date and time types

SQL date and time types store moments in time at various precisions and with various ranges. The three worth recognizing in MySQL:

• DATE — stores a year-month-day in 3 bytes. Range covers from year 1000 to year 9999. No time of day.
• DATETIME — stores a year-month-day-hour-minute-second in 5 bytes (plus 0-3 bytes for fractional seconds in MySQL 5.6+). Same wide range as DATE.
• TIMESTAMP — stores a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) in 4 bytes. Range is 1970 to 2038 (the Year 2038 problem). When declared with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, the database fills the value on insert and refreshes it on every update, which is handy for last_modified fields. Without that clause, TIMESTAMP does not auto-update.

CREATE TABLE events (
    event_id    INT NOT NULL AUTO_INCREMENT,
    event_date  DATE,
    start_at    DATETIME,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (event_id)
);

The choice between DATETIME and TIMESTAMP is the subtle one. DATETIME stores the timestamp exactly as given, without timezone conversion: what you wrote is what you get back. TIMESTAMP stores the moment in UTC internally and converts on read to the connection’s timezone. Sometimes that’s what you want (universal moments stay correct across timezones), sometimes it bites you (the value displayed depends on session settings).

The auto-update behaviour on TIMESTAMP (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) is the standard idiom for last_modified columns. The database fills the field in on insert and updates it on every change, with no application code involved.

Beyond these three, MySQL also has TIME (just a time of day, no date) and YEAR (just a year, in 1 byte). PostgreSQL has its own slightly different set including TIMESTAMP WITH TIME ZONE and INTERVAL.

For ranges, comparisons, and arithmetic, SQL supports >, <, BETWEEN, DATE_ADD(...), DATEDIFF(...), and friends:

SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM events WHERE start_at > NOW() - INTERVAL 7 DAY;

You can also store timestamps as integers (epoch seconds) in BIGINT. That’s portable with no timezone surprises, but you lose built-in date arithmetic and the values are unreadable in raw query output. Prefer the native types unless there’s a specific reason not to.