MySQL DATETIME vs TIMESTAMP: Time Zones and the 2038 Limit

Both types hold a date and a time. They behave very differently when the time zone changes.

The key difference

DATETIME stores exactly the value you give. It does not know about time zones.
TIMESTAMP converts your value to UTC when saving, and converts back to the session’s time zone when reading.

query.sql

SQL

CREATE TABLE zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    as_datetime DATETIME,
    as_timestamp TIMESTAMP NULL
);

SET time_zone = '+00:00';
INSERT INTO zones (as_datetime, as_timestamp) VALUES ('2026-06-01 12:00:00', '2026-06-01 12:00:00');
SELECT as_datetime, as_timestamp FROM zones;

Output

OUTPUT

+---------------------+---------------------+
| as_datetime         | as_timestamp        |
+---------------------+---------------------+
| 2026-06-01 12:00:00 | 2026-06-01 12:00:00 |
+---------------------+---------------------+
1 row in set

Both show 12:00 in a UTC session. Now read the same row from a session in India (UTC+5:30):

query.sql

SQL

1
2

SET time_zone = '+05:30';
SELECT as_datetime, as_timestamp FROM zones;

Output

OUTPUT

+---------------------+---------------------+
| as_datetime         | as_timestamp        |
+---------------------+---------------------+
| 2026-06-01 12:00:00 | 2026-06-01 17:30:00 |
+---------------------+---------------------+
1 row in set

The DATETIME still says 12:00. The TIMESTAMP now says 17:30: the same moment, shown in the new zone.

What that means

Question	`DATETIME`	`TIMESTAMP`
“When did this happen, as an absolute moment?”	Not directly	Yes
“At what local clock time is the event?” (a 9:00 meeting in Pune)	Yes	Not directly
Changes with the session time zone	No	Yes
Changes if the server’s time zone setting changes	No	Only through the session zone

The year 2038 problem

TIMESTAMP is stored as a 4-byte number of seconds since 1970, so it ends on 19 January 2038. DATETIME goes to the year 9999.

query.sql

SQL

1
2

SET time_zone = '+00:00';
INSERT INTO zones (as_datetime, as_timestamp) VALUES ('2040-01-01 00:00:00', '2040-01-01 00:00:00');

Output

OUTPUT

ERROR 1292 (22007): Incorrect datetime value: '2040-01-01 00:00:00' for column 'as_timestamp' at row 1

The value was rejected for the TIMESTAMP column. For anything that may reach beyond 2038 (contract end dates, subscriptions, reminders), use DATETIME.

Size

Type	Bytes (no fractions)
`DATETIME`	5
`TIMESTAMP`	4

Fractional seconds add 1 to 3 bytes. The difference is tiny. Choose by meaning, not by size.

A strong approach: store UTC in DATETIME

Many teams store all moments in UTC in DATETIME columns, and convert to the user’s time zone in the application. It combines the wide range of DATETIME with a clear rule:

query.sql

SQL

CREATE TABLE logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    logged_in_at_utc DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP())
);
INSERT INTO logins (user_id) VALUES (1);
SELECT user_id, logged_in_at_utc IS NOT NULL AS has_time FROM logins;

Output

OUTPUT

+---------+----------+
| user_id | has_time |
+---------+----------+
|       1 |        1 |
+---------+----------+
1 row in set

A default expression such as (UTC_TIMESTAMP()) is allowed since MySQL 8.0.13. Document it with a column comment or the _utc suffix.

Converting between zones

query.sql

SQL

SELECT CONVERT_TZ('2026-06-01 12:00:00', '+00:00', '+05:30') AS india_time;

Output

OUTPUT

+---------------------+
| india_time          |
+---------------------+
| 2026-06-01 17:30:00 |
+---------------------+
1 row in set

(Named zones such as 'Asia/Kolkata' work only after the time zone tables are loaded with mysql_tzinfo_to_sql. Offsets such as '+05:30' always work, but they do not know about daylight saving.)

Recommendation

Situation	Choose
`created_at`, `updated_at`, audit logs on a single-zone system	`TIMESTAMP` or `DATETIME`, either is fine
Application with users in many time zones	`DATETIME` in UTC, convert in the app
Future or far-away dates	`DATETIME`
Local clock times (a shop opens at 09:00)	`TIME`, or `DATETIME` without zone meaning
A date with no time	`DATE`

dangerous

Set the zone on purpose

A TIMESTAMP depends on time_zone. If the server moves to another host with another default zone, or a connection sets a different zone, the displayed values change. Fix the zone in the server configuration (default-time-zone = '+00:00') and in your connection settings.