Date and Time Types

DATE, TIME, DATETIME, and TIMESTAMP

DATE (3 bytes) and DATETIME (5 bytes) span the years 1000 to 9999, and TIME (3 bytes) holds a time of day or a duration up to ±838:59:59. TIMESTAMP (4 bytes) stores a moment in UTC, converting through the session time_zone, and ends at 2038-01-19 03:14:07 UTC. Six fractional digits, as in DATETIME(6), cost up to 3 more bytes.

TIMESTAMP converts through UTC; DATETIME stores the literal wall-clock value

Time zones, the 2038 limit and fractional secondsSQL

CREATE TABLE tz (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+08:00';
INSERT INTO tz VALUES ('2026-09-23 09:00:00', '2026-09-23 09:00:00'),
                      ('2026-09-23 09:00:00-04:00', '2026-09-23 09:00:00-04:00');
SET time_zone = '+00:00';
SELECT dt, ts, UNIX_TIMESTAMP(ts) AS epoch FROM tz;
INSERT INTO tz (ts) VALUES ('2038-01-19 03:14:08');
CREATE TABLE frac (d0 DATETIME, d3 DATETIME(3), t TIME);
INSERT INTO frac VALUES ('2026-12-31 23:59:59.5', '2026-12-31 23:59:59.12345', '1130');
SELECT * FROM frac;

Output

+---------------------+---------------------+------------+
| dt                  | ts                  | epoch      |
+---------------------+---------------------+------------+
| 2026-09-23 09:00:00 | 2026-09-23 01:00:00 | 1790125200 |
| 2026-09-23 21:00:00 | 2026-09-23 13:00:00 | 1790168400 |
+---------------------+---------------------+------------+
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'ts' at row 1
+---------------------+-------------------------+----------+
| d0                  | d3                      | t        |
+---------------------+-------------------------+----------+
| 2027-01-01 00:00:00 | 2026-12-31 23:59:59.123 | 00:11:30 |
+---------------------+-------------------------+----------+

In UTC the TIMESTAMP shows different clock times; the DATETIME does not. The -04:00 offset was converted to the session zone and discarded. Strict mode rejects the second after 2038, as it does February 30. Extra fractional digits are rounded, so half a second pushed d0 into 2027, and '1130' means 00:11:30. Run connections in UTC, convert in PHP (Time Zones and DateTimeZone), and prefer DATETIME beyond 2038.

MENU

Date and Time Types