In MySQL, how do DATETIME and TIMESTAMP differ in range, storage and time-zone handling, and which suits an order's placed-at column?
answer
- one converts, one does not
- the session time_zone variable
- 1970 to 2038 versus 1000 to 9999
- 4 bytes versus 5 bytes
basics
~20 sMySQL'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 linesCREATE 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
Recall that TIMESTAMP is converted through UTC and ends in 2038, while DATETIME is stored as written and covers years 1000 to 9999.
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.
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.
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.