Databases

In brief: Most modern databases store dates in ways that last for thousands of years. The main exceptions are MySQL's TIMESTAMP column type, which stops at 19 January 2038, and any table where developers chose to store "seconds since 1970" in a 32-bit integer column. MariaDB extended its TIMESTAMP to 2106 in 2024; MySQL still has the 2038 limit for that type. Some smaller internal fields, such as MongoDB's ObjectId timestamp, run out in 2106.

Overview

Databases matter for 2038 in two ways. First, a column type can have a hard upper limit. Second, applications often store future dates long before they happen: a 20-year mortgage, a certificate expiry, a warranty end date, or a "never expires" sentinel. Those values cross 2038 now, so database limits can cause errors or silent corruption years ahead of the date itself.

The good news is that the main SQL engines' native date/time types are wide. The problems are concentrated in a few legacy types, in functions that convert to and from Unix time, and in schemas designed around 32-bit integers.

Technical details

MySQL

MySQL's TIMESTAMP stores seconds since the epoch and "has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC", while DATETIME covers '1000-01-01 00:00:00' to '9999-12-31 23:59:59' ^[1].

MySQL 8.0.28 (released 2022-01-18) changed the conversion functions, not the type ^[2]:

• FROM_UNIXTIME(), UNIX_TIMESTAMP() and CONVERT_TZ() "now handle 64-bit values on platforms that support them, including 64-bit versions of Linux, MacOS, and Windows."
• FROM_UNIXTIME() accepts up to 32536771199.999999, i.e. '3001-01-18 23:59:59.999999' UTC; beyond that it returns NULL.
• UNIX_TIMESTAMP() accepts up to '3001-01-18 23:59:59.999999' UTC; beyond that it returns 0.
• "The behavior of these 3 functions on 32-bit platforms is unchanged."
• "The behavior of the TIMESTAMP type is also unaffected by this change; its maximum allowed value remains '2038-01-19 03:14:07.999999' UTC, regardless of platform. For dates futureward of this, use the MySQL DATETIME type instead." ^[2]

A MySQL feature request to extend TIMESTAMP the way MariaDB did (bug #118267) is marked as verified but not implemented (as of 2026-10-21) ^[3]. Percona Server has not adopted the extension either ^[4].

MariaDB

MariaDB 11.5.0 (2024-03-18) extended TIMESTAMP: "The maximal allowed value for timestamps was '2038-01-19 03:14:07 UTC', and is now '2106-02-07 06:28:15 UTC'" (MDEV-32188) ^[5]. The on-disk format is unchanged, so tables remain readable by older servers while values stay in the old range, and the extension applies only on 64-bit platforms ^[5]. MariaDB 11.5 was a short-lived rolling release that is no longer maintained. The first long-term-support release with the extension is MariaDB 11.8 LTS; its announcement says no data conversion is needed except for system-versioned tables, whose rows and indexes must be rewritten during the upgrade, which can take a long time on large tables ^[15].

PostgreSQL

timestamp and timestamp with time zone are 8 bytes, range 4713 BC to 294276 AD with 1-microsecond resolution; date is 4 bytes, 4713 BC to 5874897 AD ^[6]. Native types are safe. Risk remains in to_timestamp(int4) style code paths, application columns declared integer for epochs, and extensions or clients that convert to a 32-bit time_t.

SQLite

SQLite has no dedicated date type; dates are stored as ISO-8601 text, Julian day numbers (REAL) or Unix timestamps (INTEGER) ^[7]. Its date functions work for "dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59" ^[7]. SQLite integers are up to 64-bit, so the engine is safe. The risk is in the application: code on 32-bit platforms that reads an INTEGER column into a C time_t or int.

Oracle Database

DATE has a "valid date range from January 1, 4712 BC, to December 31, 9999 AD" and the YEAR datetime field ranges from -4712 to 9999 (excluding year 0), which also governs TIMESTAMP ^[8]. Oracle native types are not affected by 2038.

Microsoft SQL Server

datetime covers 1753-01-01 through 9999-12-31 (8 bytes, rounded to .000/.003/.007 s), and Microsoft advises: "Avoid using datetime for new work" ^[9]. datetime2 covers 0001-01-01 through 9999-12-31 with 100 ns accuracy ^[10]. Native types are safe.

MongoDB

• BSON Date: "a 64-bit integer that represents the number of milliseconds since the Unix epoch … a representable date range of about 290 million years into the past and future." It is signed ^[11].
• ObjectId: begins with "A 4-byte timestamp, representing the ObjectId's creation, measured in seconds since the Unix epoch" ^[11]. The driver specification says "Drivers MUST interpret this value as an unsigned 32-bit integer", which extends it "from the year 2038 to 2106" ^[12]. Drivers that interpret it as signed will produce 1901 dates after 2038.
• Internal Timestamp type (replication oplog): a 64-bit value whose most significant 32 bits are a time_t in seconds and least significant 32 bits an ordinal ^[11]. Not meant for application data.

Redis

Key expiry is set with EXPIREAT (seconds) or PEXPIREAT (milliseconds) Unix times ^[13]. In the RDB persistence format, opcode 0xFC stores expiry as an 8-byte millisecond timestamp (RDB version 3 and later) and the older opcode 0xFD stores it as a 4-byte value in seconds ^[14]. Current Redis writes the millisecond form. Old RDB files, or third-party parsers that only handle 0xFD as 32-bit, are the only notable exposure.

Schemas that store epochs in INT

A very common pattern is created_at INT or expires INT holding Unix seconds. In MySQL, PostgreSQL and SQL Server, INT is a signed 32-bit integer (max 2,147,483,647), so it overflows exactly at 2038-01-19 03:14:07 UTC. An INT UNSIGNED (MySQL) lasts to 2106-02-07 06:28:15 UTC. ORMs and migration tools sometimes generate these columns by default.

Affected versions / products

Risk & compliance notes

• Future dates arrive early. Anything storing expiries, maturities, retention dates or "far future" sentinels (2099-12-31, 9999-12-31) already exceeds 2038. In MySQL, inserting such a value into TIMESTAMP fails or is coerced depending on SQL mode.
• Inventory: every TIMESTAMP column in MySQL/older MariaDB; every INT/INTEGER/INT UNSIGNED column holding epochs; triggers, stored procedures and ETL jobs that call UNIX_TIMESTAMP()/FROM_UNIXTIME(); client libraries on 32-bit hosts.
• Owners: DBAs for column types; application teams for epoch columns and ORM defaults; data engineering for ETL.

Detection & mitigation

Find MySQL/MariaDB TIMESTAMP and integer columns with time-like names:

SELECT table_schema, table_name, column_name, column_type
FROM information_schema.columns
WHERE data_type = 'timestamp'
   OR (data_type IN ('int','mediumint') AND column_name REGEXP '(time|date|_at$|expir|epoch|ts$)');

Find PostgreSQL integer columns with time-like names:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type = 'integer'
  AND column_name ~* '(time|date|_at$|expir|epoch|ts$)';

Remediation options:

1. MySQL: ALTER TABLE … MODIFY col DATETIME(6) (note: DATETIME has no implicit time-zone conversion, unlike TIMESTAMP; store UTC explicitly).
2. MariaDB: upgrade to 11.8 LTS or later on a 64-bit platform, allowing time for system-versioned tables to be rewritten ^[15], or convert to DATETIME.
3. Epoch columns: ALTER … TYPE BIGINT (PostgreSQL) / MODIFY … BIGINT (MySQL), then fix application code to use 64-bit integers.
4. Test by inserting '2038-01-19 03:14:08' and '2106-02-07 06:28:16' into staging copies and running application flows.

See also Testing-and-Detection and Remediation-Guide.

See also

• File-Systems · File-Formats-and-Protocols
• Lang-PHP · Lang-C-and-CPP · Programming-Languages
• Year-2106-and-Beyond

References

1. MySQL 8.0 Reference Manual §13.2.2, The DATE, DATETIME, and TIMESTAMP Types — Oracle — TIMESTAMP and DATETIME ranges.
2. MySQL 8.0.28 Release Notes — Oracle, 2022-01-18 — WL #14630: 64-bit FROM_UNIXTIME/UNIX_TIMESTAMP/CONVERT_TZ; TIMESTAMP unchanged.
3. MySQL bug #118267 — bugs.mysql.com — feature request to extend TIMESTAMP range (seen in search results).
4. Can we extend timestamp range … as MariaDB did? — Percona Community Forum — Percona Server still has the 2038 limit (seen in search results).
5. MariaDB 11.5.0 Release Notes — MariaDB, 2024-03-18 — MDEV-32188 TIMESTAMP to 2106; 64-bit only; format unchanged.
6. PostgreSQL 18 documentation §8.5 Date/Time Types — PostgreSQL Global Development Group — Table 8.9 ranges.
7. Date And Time Functions — SQLite — storage forms and 0000–9999 range.
8. Oracle Database 19c SQL Language Reference, Data Types — Oracle — DATE range 4712 BC–9999 AD.
9. datetime (Transact-SQL) — Microsoft Learn — range 1753–9999; "Avoid using datetime for new work".
10. datetime2 (Transact-SQL) — Microsoft Learn — range 0001–9999, 100 ns.
11. BSON Types — MongoDB Manual — Date, ObjectId and internal Timestamp definitions.
12. ObjectId specification — mongodb/specifications (GitHub) — timestamp is unsigned 32-bit, good to 2106.
13. PEXPIREAT — Redis documentation — absolute expiry in Unix milliseconds (seen in search results).
14. Redis RDB File Format — rdb.fnordig.de — 0xFD 4-byte seconds, 0xFC 8-byte milliseconds expiry opcodes (seen in search results).
15. MariaDB 11.8 LTS Released — MariaDB Foundation, 2025 — extended TIMESTAMP in an LTS release; system-versioned tables need rewriting (seen in search results).