When should you NOT use MySQL Date and Time Types?

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 is MySQL Date and Time Types the right choice?

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.

← Back to glossary

MySQL Date and Time Types

Database Beginner

debt(d5/e3/b5/t7)

d5 Detectability Operational debt — how invisible misuse is to your safety net

Closest to 'specialist tool catches it' (d5). The detection_hints list semgrep as the tool, with a specific code_pattern for TIMESTAMP columns on long-lived data. This is not caught by default linters but requires a configured semgrep rule, placing it squarely at d5.

e3 Effort Remediation debt — work required to fix once spotted

Closest to 'simple parameterised fix' (e3). The quick_fix states 'Use DATETIME for flexibility and future-proofing, store values in UTC, handle timezone conversion in PHP' — this is a column type swap plus a consistent application-layer convention. It touches the schema and potentially multiple model files but follows a clear replace-pattern approach, not an architectural rework.

b5 Burden Structural debt — long-term weight of choosing wrong

Closest to 'persistent productivity tax' (b5). The choice applies to web and CLI contexts broadly. TIMESTAMP columns used in long-lived tables impose an ongoing burden: every developer must know the Y2038 limit, the server-timezone dependency, and the UTC storage convention. Incorrect choices affect queries, reporting, and cross-environment comparisons across multiple work streams.

t7 Trap Cognitive debt — how counter-intuitive correct behaviour is

Closest to 'serious trap' (t7). The misconception field states TIMESTAMP 'handles timezones automatically so you don't need to think about them' — but server timezone changes silently reinterpret all stored values. Additionally, the Y2038 wrap is silent in production until the date is reached. These traps contradict reasonable expectations from similar datetime types in other databases and languages, justifying t7.

About DEBT scoring → scored by claude-sonnet-4-6 · 2026-05-10 · reviewed by human

Also Known As

MySQL DATETIME vs TIMESTAMP MySQL date types TIMESTAMP Y2038 MySQL time storage

TL;DR

DATETIME, TIMESTAMP, DATE, TIME, and YEAR — each with different ranges, timezone handling, and storage sizes.

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

⚠ TIMESTAMP columns hit Y2K38 on 2038-01-19 03:14:07 UTC — stored value wraps to NULL or 0 depending on strict mode.

Common Misconception

✗ TIMESTAMP handles timezones automatically so you don't need to think about them. TIMESTAMP converts using the MySQL server's timezone setting — if that changes, all stored timestamps display differently.

Why It Matters

TIMESTAMP columns silently hit Y2K38 — January 19, 2038, values wrap to 0. For data that will exist past 2038, DATETIME is required. Timezone confusion between DATETIME (no conversion) and TIMESTAMP (converted) causes display bugs.

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

✗ Vulnerable

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

✓ Fixed

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