Dates and times

On 19 January 2038, a 32-bit clock runs out of numbers

A counter that fit easily in 1970 has a hard stop. The risk sits in old columns, file formats and devices nobody updates.

Published 5 min read

Jump to the database tests
A Seiko digital wall clock showing the time, date and temperature

Photo: LCD digital wall clock (Seiko), Mk2010, CC BY-SA 3.0, via Wikimedia Commons.

Unix time counts seconds from 00:00:00 UTC on 1 January 1970. If a system stores that count in a signed 32-bit integer, the largest value it can hold is 2,147,483,647. That moment is 03:14:07 UTC on 19 January 2038. One second later the number no longer fits. The sign bit flips, the value reads as negative, and the date turns into 20:45:52 UTC on 13 December 1901.

Most current operating systems use 64-bit time, which will not run out for roughly 292 billion years. The risk is in what is left behind: old column types, file formats, embedded devices and libraries that still assume 32 bits. As Wikipedia puts it, the most vulnerable systems are those which are infrequently or never updated.

Where the limit still shows up

PlaceWhat the reference says
MySQL TIMESTAMP columnsThe documented range is 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. Later dates cannot be stored in that type.
MySQL functionsBefore version 8.0.28, UNIX_TIMESTAMP() returned 0 after 03:14:07. Version 8.0.28 added 64-bit handling on supported platforms.
File systemsext2, ext3 and ReiserFS use 32 bits for inode times. ext4 extends its range to 2446 only when inodes are larger than 128 bytes.
NFSVersion 3 uses unsigned 32-bit seconds. Version 4 uses 64-bit seconds fields.
Linux on 32-bit hardwareLinux 5.6, in 2020, added support for 64-bit time_t on 32-bit architectures, mainly for embedded systems.
Embedded devicesOften never updated, and sometimes impossible to update.

Whether any of that applies to you depends on which versions you run. The table is a list of places to look, not a prediction that they have failed.

Test what your database does

SQLite stores integers up to 64 bits, so it counts straight past 2038. This query shows the last second a 32-bit counter can hold, the next one, and the lowest 32-bit value:

SQL
SELECT datetime(2147483647, 'unixepoch')  AS last_32bit_second,
       datetime(2147483648, 'unixepoch')  AS next_second,
       datetime(-2147483648, 'unixepoch') AS lowest_32bit;
-- 2038-01-19 03:14:07 | 2038-01-19 03:14:08 | 1901-12-13 20:45:52

PostgreSQL's date types are 64-bit, but an ordinary integer column holding epoch seconds is 32-bit. This run, on PostgreSQL 16, shows a timestamp converting without trouble and an integer column refusing the value:

SQL
SELECT to_timestamp(2147483648) AT TIME ZONE 'UTC';   -- 2038-01-19 03:14:08

CREATE TABLE sessions (id int, expires_at int);
INSERT INTO sessions VALUES (1, 2147483647);          -- works
INSERT INTO sessions VALUES (2, 2147483648);
-- ERROR:  integer out of range

That error is the good outcome, because it is loud. The dangerous case is a system that silently wraps or truncates the value. Test with your actual stack, not an assumption about it.

Find the fragile columns

In PostgreSQL, this query lists integer-typed columns whose names look like times. It is a starting list, not proof of a bug:

SQL
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('integer', 'int4')
  AND (column_name ILIKE '%\_at' OR column_name ILIKE '%time%'
       OR column_name ILIKE '%expire%' OR column_name ILIKE '%epoch%')
ORDER BY table_schema, table_name;

In MySQL, look for columns of type TIMESTAMP that hold future dates, such as contract ends or subscription expiries, and for integer columns that hold epoch seconds.

  • Check dates far in the future: a 30-year loan, a long warranty or a certificate expiry.
  • Check where timestamps leave the database: exports, APIs, message queues and file formats.
  • Check devices and firmware that write timestamps, and which ones can be updated.
  • Ask vendors in writing whether their products handle dates after 2038.

Plan the migration

  1. 01Take a backup and test the restoreA type change rewrites data. Be sure you can come back.
  2. 02Widen on a copy firstIn PostgreSQL, change integer to bigint, or to a native timestamp type. Time how long it takes, because it may rewrite the table.
  3. 03Update every reader and writerThe column is the easy part. APIs, clients and reports may also assume 32 bits.
  4. 04Add a test with a far dateKeep a row dated well beyond 2038 in your test data so a regression fails loudly.
SQL
ALTER TABLE sessions ALTER COLUMN expires_at TYPE bigint;

Watch the wrap happen

You can reproduce the overflow without waiting until 2038. Python's ctypes module has a signed 32-bit integer type that behaves the way a 32-bit counter does. This was run to confirm the numbers.

Python
import ctypes, datetime

print(ctypes.c_int32(2147483647).value)   # 2147483647
print(ctypes.c_int32(2147483648).value)   # -2147483648  (the wrap)

utc = datetime.timezone.utc
print(datetime.datetime.fromtimestamp(ctypes.c_int32(2147483648).value, tz=utc))
# 1901-12-13 20:45:52+00:00
print(datetime.datetime.fromtimestamp(2147483648, tz=utc))
# 2038-01-19 03:14:08+00:00  (what a 64-bit counter says)

The first line is the problem in one picture: add one to the largest value and you get the smallest. A system that stores that value will then believe it is 1901. Anything that compares dates, such as a session that expires "after now", a certificate that is "valid until" or a job scheduled for "tomorrow", can behave in surprising ways when the clock jumps back by 136 years.

What the failure looks like in an application

  • Tokens or sessions that should last for years appear to be expired the moment they are created.
  • Records sort in the wrong order, because a 2038 date now sorts before a 2037 one.
  • A scheduled job never runs, or runs constantly, because its next run time is in the past.
  • An import rejects rows with "date out of range" errors, or silently stores zero.

You do not need to wait for the clock to find these. Create a row with a date in 2040 and watch what each system does with it. If any step in the chain truncates it, you have found a 32-bit assumption.

Questions to put to software and device vendors

QuestionWhy it matters
Which time type does the product use internally?A 64-bit type is safe in the foreseeable future. A signed 32-bit type is not.
Does it store dates after 2038 in files and databases?Test a contract or warranty date in 2040.
Which version fixed it, and can we upgrade?The MySQL functions mentioned earlier were fixed in a specific release.
Is the device still supported?An unsupported device with a 32-bit clock cannot be fixed by you.

Put the answers in writing and keep them. For products that cannot be changed, plan their replacement before the date, not after.

Questions that come up

Will everything break in 2038?
No. Most current systems use 64-bit time. The danger is concentrated in older and embedded software that nobody has audited.
Is the 2038 problem the same as Y2K?
Similar in spirit: a stored value was too small for the future. The limit is different and the fix is a wider type, not a different date format.
Does my SQLite or PostgreSQL database have this problem?
The engines handle 64-bit values, as the tests above show. Your own schema may still hold times in 32-bit integer columns, and the programs reading them may too.
Is there a related deadline?
The Wikipedia article notes that an NTP timestamp rollover in 2036 is similar but not identical.

Sources