Description:
1. Summary
The MySQL TIMESTAMP data type is internally stored as a 32-bit signed integer representing seconds since the Unix epoch (1970-01-01 00:00:00 UTC). This representation overflows on 2038-01-19 at 03:14:07 UTC — the well-known Year 2038 Problem (Y2K38).
We request that MySQL extend TIMESTAMP storage to 64-bit signed integer (or equivalent), eliminating the 2038 overflow while preserving all existing TIMESTAMP semantics: UTC storage, automatic DST-aware conversion on read/write, and time zone session awareness.
MariaDB has already implemented this change. MySQL — the most widely deployed open-source RDBMS — should follow suit.
2. Problem Description
2.1 The Year 2038 Hard Deadline
The current TIMESTAMP range is:
1970-01-01 00:00:01 UTC → 2038-01-19 03:14:07 UTC
Any application storing future dates beyond this boundary — contracts, subscriptions, warranties, scheduling systems, financial instruments, IoT sensor data, legal deadlines — is forced into workarounds today. Systems that will still be running in 2038 need a solution now, because data migration at the overflow moment is not feasible at scale.
2.2 Why DATETIME Is NOT an Acceptable Substitute
The MySQL documentation and community often suggests DATETIME as a workaround. This is technically incorrect for any application that must handle Daylight Saving Time (DST) transitions correctly.
DATETIME stores a "wall clock" value with no time zone information. This creates a critical ambiguity during the DST fall-back transition:
Example — Central European Time (CET/CEST), DST ends last Sunday of October:
-- The clock is turned back from 03:00 to 02:00.
-- The value '2024-10-27 02:30:00' occurs TWICE:
-- once at 00:30 UTC (CEST, UTC+2)
-- once at 01:30 UTC (CET, UTC+1)
If this value is stored as DATETIME, it is completely ambiguous. There is no way to determine from the stored data alone which of the two physical moments it refers to. For log entries, financial transactions, sensor readings, audit trails, and event scheduling, this ambiguity is not acceptable.
TIMESTAMP does NOT have this problem. Because it stores UTC internally and converts on read using the session time zone, each physical moment maps to exactly one stored value — even across DST transitions. The two moments above are stored as distinct values:
2024-10-27 00:30:00 UTC → stored as Unix timestamp 1729985400
2024-10-27 01:30:00 UTC → stored as Unix timestamp 1729989000
When read back in Europe/Warsaw time zone, each returns the correct local wall-clock time with no ambiguity. DATETIME cannot replicate this behavior without storing an additional time zone column — adding complexity, storage overhead, and the need for application-level logic.
2.3 DST Spring-Forward: The Skipped Hour
The reverse problem occurs during DST spring-forward. When clocks jump forward from 02:00 to 03:00, times between 02:00:00 and 02:59:59 local time do not exist. Any application inserting a DATETIME in this range inserts a value that corresponds to no real moment in time. TIMESTAMP prevents this by operating in UTC, where no such skips occur.
2.4 Comparison Table: TIMESTAMP vs DATETIME
Feature TIMESTAMP DATETIME
-----------------------------------------------------------------------------------------------
Stores UTC internally YES NO
DST-aware conversion on read YES NO
Unambiguous across DST fall-back YES — unique Unix value per moment NO — same value for two distinct moments
No skipped times (spring-forward) YES — UTC has no gaps NO — wall clock gaps stored as-is
Automatic session TZ conversion YES NO
Range beyond 2038 NO (current) YES (up to 9999)
Storage size 4 bytes (current) 5–8 bytes
3. Precedent: MariaDB Already Solved This
MariaDB — a direct fork of MySQL with a shared codebase history — has already extended TIMESTAMP to avoid the 2038 overflow. Their implementation extends the internal storage to support dates well beyond 2038 while maintaining full backward compatibility with existing TIMESTAMP semantics.
Reference: MariaDB TIMESTAMP documentation and MDEV-10992.
The fact that MariaDB has done this demonstrates:
• The change is technically feasible within the MySQL/MariaDB codebase architecture.
• Backward compatibility can be maintained — existing TIMESTAMP columns and queries continue to work unchanged.
• The semantic contract of TIMESTAMP (UTC storage, TZ-aware conversion, DST correctness) is fully preserved.
MySQL users who need both DST correctness AND a range beyond 2038 currently have no solution within MySQL. They must either migrate to MariaDB or PostgreSQL (which uses 64-bit timestamps natively), or implement expensive and error-prone application-layer workarounds.
4. Proposed Solution
4.1 Primary Proposal
Extend TIMESTAMP internal storage from 32-bit signed integer to 64-bit signed integer.
4.2 Acceptable Minimum
If a 64-bit change is considered too large in scope, a minimum acceptable solution would be an unsigned 32-bit representation extended to cover the range up to 2106-02-07, or a new extended type (e.g., TIMESTAMP6 or TIMESTAMP EXTENDED) that is semantically identical to TIMESTAMP but uses 64-bit storage.
4.3 Storage Consideration
The storage increase from 4 bytes to 8 bytes per row is a known trade-off. For the vast majority of use cases, this is completely acceptable. If storage efficiency is a concern, MySQL could offer both a legacy TIMESTAMP (4-byte, range until 2038, for backward compat) and TIMESTAMP EXTENDED or simply update TIMESTAMP in a new major version with appropriate migration tooling.
5. Real-World Use Cases Blocked by This Limitation
• Contracts, bonds, and derivatives with maturity dates beyond 2038. Financial systems:
• Devices deployed today with operational lifetimes of 20–30 years storing sensor timestamps. IoT and embedded systems:
• "Lifetime" or long-term subscriptions, license expiry dates. Subscription and SaaS platforms:
• Document retention policies, statute of limitations tracking. Legal and compliance systems:
• Long-term patient records, drug expiry tracking, appointment scheduling systems. Healthcare:
• Certificate expiry, scheduled maintenance, infrastructure lifecycle management. Infrastructure:
• Requires unambiguous timestamps across DST transitions — DATETIME cannot guarantee this. Any log/audit system:
6. Risk of Inaction
The 2038 deadline is not hypothetical — it is 13 years away. MySQL instances deployed today that are expected to remain in production through 2038 already need a solution. The longer this is deferred, the more systems will accumulate TIMESTAMP columns that will require emergency migration under time pressure.
Without a fix in MySQL:
• MySQL loses competitive parity with MariaDB and PostgreSQL on a fundamental data type.
• Teams working on long-lived systems will be pushed toward alternatives.
• DST-sensitive applications will continue to use DATETIME incorrectly, leading to subtle data integrity bugs that are difficult to diagnose.
7. References
• MariaDB TIMESTAMP documentation: https://mariadb.com/kb/en/timestamp/
• MariaDB MDEV-10992 (timestamp extension): https://jira.mariadb.org/browse/MDEV-10992
• Year 2038 Problem (Wikipedia): https://en.wikipedia.org/wiki/Year_2038_problem
• PostgreSQL TIMESTAMP: 8-byte storage, range 4713 BC to 5874897 AD — https://www.postgresql.org/docs/current/datatype-datetime.html
• MySQL Bug #12654 (related, long-standing request): https://bugs.mysql.com/bug.php?id=12654
• MySQL Bug #105955 (related, long-standing request): https://bugs.mysql.com/bug.php?id=105955
How to repeat:
Change the time on the server to 2038-01-19 03:14:07 UTC or beyond?
Suggested fix:
64 BIT timestamp or just change current 32 BIT signed implementation to the 32 unsigned implementation like MariadDB has done.
