✏️ Explanatory Question
Level: Basic — A subtle but important question about how MySQL stores date-time values and time zones.
Both DATETIME and TIMESTAMP store date and time values in the format YYYY-MM-DD HH:MM:SS, but they differ in range, storage size, and time-zone handling.
1000-01-01 to 9999-12-31. Uses 8 bytes (5 in newer versions).1970-01-01 to 2038-01-19 (the "Year 2038 problem"). Uses 4 bytes.| Feature | DATETIME | TIMESTAMP |
|---|---|---|
| Storage size | 8 bytes (5 in MySQL 5.6+) | 4 bytes |
| Range | 1000 – 9999 | 1970 – 2038 |
| Time-zone conversion | No | Yes (stored as UTC) |
| Auto-update on change | Only if defined | Can auto-update |
| Default value | Any valid datetime | CURRENT_TIMESTAMP common |
| Best for | Fixed events (birthdays, appointments) | Record timestamps (created/updated) |
TIMESTAMP is stored as a 32-bit integer counting seconds since 1970-01-01 (Unix epoch). On January 19, 2038, this integer overflows — so for far-future dates, prefer DATETIME.
CREATE TABLE events (
id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(100),
event_time DATETIME, -- stored as-is
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- auto set on insert
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP -- auto updates on change
);
INSERT INTO events (event_name, event_time)
VALUES ('Launch', '2026-12-25 10:00:00');
-- Changing the session time zone affects TIMESTAMP display, not DATETIME
SET time_zone = '+00:00';
SELECT event_time, created_at FROM events;