skip to content

In MySQL, how do DATETIME and TIMESTAMP differ in range, storage and time-zone handling, and which suits an order's placed-at column?

level: middleimportance: must knowfreq 66%

answer

  1. one converts, one does not
  2. the session time_zone variable
  3. 1970 to 2038 versus 1000 to 9999
  4. 4 bytes versus 5 bytes

basics

~20 s

MySQL's TIMESTAMP converts values from the session time zone to UTC and back and only covers 1970 to 2038; DATETIME stores exactly what you give it from year 1000 to 9999. For order times, DATETIME written in UTC is usually the safer choice.

solid answer

~40 s

`TIMESTAMP` takes 4 bytes plus fractional seconds and covers `'1970-01-01 00:00:01'` to `'2038-01-19 03:14:07'` UTC. MySQL converts it from the session's `time_zone` to UTC on write and back on read, so the same row displays differently to sessions in different zones. `DATETIME` takes 5 bytes plus fractional seconds, covers `'1000-01-01 00:00:00'` to `'9999-12-31 23:59:59'` and is never converted: you get back exactly what you stored. Both accept `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` and up to six fractional digits. For an order's placed-at time I'd use `DATETIME(3)` or `DATETIME(6)` with a firm rule that the application writes UTC, because it avoids the 2038 ceiling and does not change meaning if a server's `time_zone` changes. `TIMESTAMP` fits when you want per-session conversion and the range is acceptable.

code

sql · 12 lines
sql
CREATE TABLE tz_demo (
  ts TIMESTAMP NOT NULL,
  dt DATETIME NOT NULL
);

SET time_zone = '+00:00';
INSERT INTO tz_demo VALUES ('2026-03-01 12:00:00', '2026-03-01 12:00:00');

SET time_zone = '+05:30';
SELECT ts, dt FROM tz_demo;
-- ts: 2026-03-01 17:30:00   (converted from UTC)
-- dt: 2026-03-01 12:00:00   (returned as stored)

go deeper

for a junior

Recall that TIMESTAMP is converted through UTC and ends in 2038, while DATETIME is stored as written and covers years 1000 to 9999.

for a middle

Explain the session time_zone conversion, the storage sizes and fractional-second options, and why explicit_defaults_for_timestamp matters when reading an old schema.

for a senior

Show how a time_zone change or mixed connection settings corrupt reports, and state the UTC convention and connection settings you enforce to prevent it.

for a principal

Weigh a fleet-wide date-time policy: one UTC convention and type per kind of value, and the migration cost for tables that already mix the two.

## Two date-time types with different contracts MySQL has two column types that hold a date plus a time of day. They look interchangeable in a `SELECT`, but they make different promises: - **`DATETIME`** stores a calendar date and wall-clock time exactly as written. MySQL never converts it. - **`TIMESTAMP`** stores an instant. MySQL converts the value from the **current session time zone** to UTC when it writes, and from UTC back to the session's zone when it reads. The session time zone is the `time_zone` system variable. Its default is `SYSTEM`, meaning the server host's zone, and any connection can change it with `SET time_zone = '+00:00'` or a named zone if the zone tables are loaded. ## Range, storage and precision | | `DATETIME` | `TIMESTAMP` | |---|---|---| | Supported range | `'1000-01-01 00:00:00'` to `'9999-12-31 23:59:59'` | `'1970-01-01 00:00:01'` UTC to `'2038-01-19 03:14:07'` UTC | | Storage (no fractional part) | 5 bytes | 4 bytes | | Fractional seconds | up to 6 digits, 0 to 3 extra bytes | up to 6 digits, 0 to 3 extra bytes | | Time-zone conversion | none | session zone to UTC on write, back on read | | `DEFAULT CURRENT_TIMESTAMP` / `ON UPDATE CURRENT_TIMESTAMP` | allowed | allowed | A few points that follow: - **The 2038 ceiling is a property of the column type.** The `FROM_UNIXTIME()` function accepts larger values on 64-bit platforms, but a `TIMESTAMP` column still cannot hold anything after `'2038-01-19 03:14:07'` UTC. A subscription that expires in 2040 cannot be stored in it. - **Out-of-range and invalid values.** In strict mode an invalid value is rejected; without strict mode, MySQL may store the "zero" value `'0000-00-00 00:00:00'` instead, depending on `NO_ZERO_DATE`. MySQL 8.4's default `sql_mode` includes strict mode, `NO_ZERO_DATE` and `NO_ZERO_IN_DATE`. - **Fractional seconds are opt-in.** `DATETIME` with no precision drops the fraction; `DATETIME(3)` keeps milliseconds and `DATETIME(6)` microseconds, for one to three extra bytes. ## How the conversion bites in production Because `TIMESTAMP` converts on both write and read, it is only stable while every session uses the same zone. Typical incidents: 1. A server moved to a host in a different zone with `time_zone=SYSTEM`. Every `TIMESTAMP` column now **displays** shifted times, while `DATETIME` columns are unchanged. Reports that mix the two disagree. 2. Two services connect with different `time_zone` settings. One writes `10:00` in its zone, the other reads the same instant as a different wall-clock time. That is correct behaviour for `TIMESTAMP` but a surprise if the team thought it was storing wall-clock text. 3. A team stores local wall-clock times in `DATETIME` without recording the zone. Daylight-saving transitions make some values ambiguous, and there is no way to recover the instant later. The cure for all three is a **convention**, not a type: store instants in UTC and convert at the edge. With `DATETIME`, the application or the connection (`SET time_zone = '+00:00'`) guarantees UTC. With `TIMESTAMP`, every connection must agree on its zone, ideally UTC too. ## Legacy TIMESTAMP behaviours Older MySQL releases gave `TIMESTAMP` columns surprising automatic properties: the first `TIMESTAMP` column in a table silently received `DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`, and assigning `NULL` set it to the current time. In MySQL 8.4 the `explicit_defaults_for_timestamp` variable is `ON` by default, which disables those behaviours, so a `TIMESTAMP` column only auto-initialises or auto-updates when you declare it. The variable itself is deprecated, and turning it off raises a warning. ## Choosing for an orders table For the orders table's `placed_at`, `paid_at` and `shipped_at`: - **`DATETIME(6)` in UTC** is the common choice: wide range, no hidden conversion, one extra byte per column over `TIMESTAMP` at that precision. - **`TIMESTAMP`** is reasonable for short-lived audit columns such as `updated_at` when every connection pins its zone, and the four-byte size matters only on very wide tables. - **Business dates without a time**, such as a delivery date promised to a customer, belong in `DATE`, and a wall-clock appointment in a customer's zone needs the zone stored beside it. Whichever type you pick, write the zone policy down; the type alone does not make the data unambiguous.

  • What happens if a MySQL application stores a 2040 contract-end time in a TIMESTAMP column?
    The value is outside TIMESTAMP's range, which ends at '2038-01-19 03:14:07' UTC. In strict mode, the default in MySQL 8.4, the statement fails as an invalid value; without strict mode MySQL can store the zero value instead. A DATETIME column, which runs to the year 9999, stores it.
  • Why might every TIMESTAMP value in a MySQL report shift by an hour after a server migration while DATETIME values stay the same?
    TIMESTAMP values are stored in UTC and converted to the session time zone when read. If time_zone is SYSTEM and the new host runs in a different zone, every read converts to the new zone. DATETIME is returned exactly as stored, so it does not move. Pinning time_zone, ideally to UTC, removes the drift.

  • A MySQL TIMESTAMP column stores the time zone alongside the value
  • MySQL converts DATETIME values to UTC just as it does TIMESTAMP
  • On 64-bit servers the TIMESTAMP column type runs past 2038
  • DATETIME cannot use DEFAULT CURRENT_TIMESTAMP, only TIMESTAMP can
  • Changing the server time_zone rewrites the TIMESTAMP values stored on disk

Assumes MySQL 8.4, where explicit_defaults_for_timestamp is ON by default (and deprecated) and the default sql_mode includes strict mode and NO_ZERO_DATE.