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 — convenient 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 subtle. 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, which is sometimes convenient (universal moments stay correct across timezones) and sometimes confusing (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;

For more raw approaches — storing timestamps as integers (epoch seconds) in BIGINT — there are pros (portable, no timezone surprises) and cons (no built-in arithmetic, harder to read in raw query output). The native types are generally preferable unless there’s a specific reason.