MySQL – Date and Time Types

MySQL has five date and time types. Picking between them is mostly one decision — whether the value should move when the time zone changes — and getting that decision wrong is the kind of bug that shifts every timestamp in your database by a few hours without raising an error.

The five types

SELECT CAST('2024-03-15 14:30:45.123456' AS DATETIME(6)) AS dt,
       CAST('2024-03-15' AS DATE) AS d,
       CAST('14:30:45' AS TIME) AS t;

+----------------------------+------------+----------+
| dt                         | d          | t        |
+----------------------------+------------+----------+
| 2024-03-15 14:30:45.123456 | 2024-03-15 | 14:30:45 |
+----------------------------+------------+----------+

TIME is worth a second look: its range is ±838 hours, well beyond a clock. It represents a duration as readily as a time of day.

DATETIME versus TIMESTAMP

This is the whole lesson. DATETIME stores the literal wall-clock value you gave it and hands it back unchanged. TIMESTAMP converts to UTC on the way in and back to the session's time zone on the way out.

CREATE TABLE tz_demo (dt DATETIME, ts TIMESTAMP NULL);
SET time_zone = '+00:00';
INSERT INTO tz_demo VALUES ('2024-06-01 12:00:00', '2024-06-01 12:00:00');
SELECT 'stored at +00:00' AS note, dt, ts FROM tz_demo;

+------------------+---------------------+---------------------+
| note             | dt                  | ts                  |
+------------------+---------------------+---------------------+
| stored at +00:00 | 2024-06-01 12:00:00 | 2024-06-01 12:00:00 |
+------------------+---------------------+---------------------+

Identical so far. Now read the same row from a session in a different zone — nothing has been written, only the reader has moved:

SET time_zone = '-07:00';
SELECT 'read at -07:00' AS note, dt, ts FROM tz_demo;

+----------------+---------------------+---------------------+
| note           | dt                  | ts                  |
+----------------+---------------------+---------------------+
| read at -07:00 | 2024-06-01 12:00:00 | 2024-06-01 05:00:00 |
+----------------+---------------------+---------------------+

DATETIME is still noon. TIMESTAMP is 05:00, because 12:00 UTC is 05:00 at -07:00. Both are correct; they answer different questions:

• Use TIMESTAMP for a moment in time — when something happened. Two people in different countries should agree it was the same instant.
• Use DATETIME for a wall-clock value that should not move. A restaurant opening at 09:00 opens at 09:00 whoever is asking.

The pizza schema stores DATETIME(6) throughout. Its Java entities map to LocalDateTime, which carries no zone either, so the pairing is consistent — and the application explicitly turns off the driver's zone conversion to keep it that way. That story, and the seven hours it cost, is in connections.

2038

TIMESTAMP is a 4-byte signed count of seconds since 1970, so it runs out on 19 January 2038. That is not far away for a column holding a subscription end date or a scheduled job. DATETIME goes to the year 9999. If you need zone-awareness past 2038, store a DATETIME in UTC and convert in the application, or use BIGINT epoch milliseconds.

Fractional seconds

Both types take a precision argument from 0 to 6: DATETIME(6) keeps microseconds, DATETIME alone keeps none and rounds — 12:00:00.6 becomes 12:00:01, which is a surprising place to lose a second.

The pizza schema uses DATETIME(6) for created/updated columns for a practical reason: two rows written in the same second are otherwise indistinguishable, and "the most recent one" stops being answerable. It costs three extra bytes.

Defaults that maintain themselves

CREATE TABLE audit_demo (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                           ON UPDATE CURRENT_TIMESTAMP(6)
);

ON UPDATE CURRENT_TIMESTAMP refreshes the column on every modification, with no trigger and no application code.

The pizza schema deliberately does not use it, and the reason is worth knowing: its timestamps are maintained by JPA lifecycle callbacks instead, so the behaviour lives in one place, in the Java, and works identically on every database. Splitting it — some columns maintained by the database, some by the application — is how you end up debugging why one of them did not update. Either mechanism is fine; pick one.

Writing dates

Use 'YYYY-MM-DD' and 'YYYY-MM-DD HH:MM:SS'. MySQL accepts a remarkable variety of other formats and its guesses are not always yours. For anything else, parse explicitly with STR_TO_DATE:

SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS parsed;

Under the default sql_mode an impossible date is an error rather than a silent zero. Older MySQL accepted '0000-00-00', which is not a date and breaks every client library that tries to read it; if you meet one in a legacy schema, it is NO_ZERO_DATE being off.

The indexing trap

-- cannot use an index on created_at
SELECT COUNT(*) FROM customer_order WHERE DATE(created_at) = '2024-06-01';

-- can
SELECT COUNT(*) FROM customer_order
WHERE  created_at >= '2024-06-01' AND created_at < '2024-06-02';

Wrapping the column in a function makes the index unusable, because the index stores created_at and the query asks about DATE(created_at). Half-open ranges are the habit worth forming — and see BETWEEN for why BETWEEN on a DATETIME quietly loses most of the last day.

Pulling a date apart

SELECT DATE('2024-03-15 14:30:45') AS date_part,
       TIME('2024-03-15 14:30:45') AS time_part,
       YEAR('2024-03-15') AS y, MONTH('2024-03-15') AS m, DAY('2024-03-15') AS d;

+------------+-----------+------+------+------+
| date_part  | time_part | y    | m    | d    |
+------------+-----------+------+------+------+
| 2024-03-15 | 14:30:45  | 2024 |    3 |   15 |
+------------+-----------+------+------+------+

Formatting and arithmetic — DATE_FORMAT, DATE_ADD, DATEDIFF, TIMESTAMPDIFF — are covered in DATE_FORMAT and date functions.

What to remember

• DATETIME does not move with the time zone; TIMESTAMP does.
• TIMESTAMP ends in 2038.
• Add (6) unless you want your times rounded to the second.
• Keep the column bare in WHERE, and use half-open ranges.