MySQL Date and Time Types

Also Known As

TL;DR

Explanation

DATETIME stores date+time without timezone conversion (range: 1000-01-01 to 9999-12-31). TIMESTAMP stores as UTC and converts to the server timezone on retrieval (range: 1970-2038). TIMESTAMP auto-updates on row modification with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. TIMESTAMP's 2038 problem is significant for long-lived data — use DATETIME for dates beyond 2038. Always store dates in UTC and convert for display in PHP.

Watch Out

Common Misconception

Why It Matters

Common Mistakes

• Using TIMESTAMP for birth dates or historical records — wraps at 2038.
• Relying on MySQL timezone conversion instead of storing UTC explicitly.
• Comparing DATETIME columns across different timezone servers and getting wrong results.

Avoid When

• Do not use TIMESTAMP for dates beyond 2038 — use DATETIME.
• Do not rely on MySQL's timezone conversion — set the application timezone explicitly and store UTC.

When To Use

• Use DATETIME for all date/time storage — store values in UTC, convert to local timezone in application code.
• Use DATETIME ON UPDATE CURRENT_TIMESTAMP for updated_at audit columns.

Code Examples

-- TIMESTAMP wraps at 2038-01-19 — don't use for long-lived data
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Y2K38 problem

CREATE TABLE events (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(255) NOT NULL,
    starts_at  DATETIME NOT NULL, -- store in UTC
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

// PHP: store in UTC, convert for display
$utc = new DateTimeImmutable('now', new DateTimeZone('UTC'));
$stmt->execute([$utc->format('Y-m-d H:i:s')]);

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Tags

Related Terms