Unix Time: Seconds Since 1970, and Where It Goes Wrong

A Unix timestamp counts seconds since 1970-01-01 00:00:00 UTC. It is a single integer with no time zone, no locale and no ambiguity about format, which is why it is the right thing to store and the wrong thing to show anyone.

Recognising what you are holding

Timestamps arrive in several units and mixing them is the most common bug in this area.

JavaScript's Date.now() returns milliseconds; most Unix tooling, databases and APIs use seconds. Feeding one to something expecting the other lands you either in 1970 or fifty thousand years out, and both are obvious — the dangerous case is silently dividing and losing precision.

It does not count every second

Unix time pretends every day has exactly 86,400 seconds. Real time has occasionally had a leap second inserted, and rather than represent it, Unix time repeats or smears a value. The consequence is that the difference between two timestamps is not exactly the elapsed physical time across a leap second.

This matters almost nowhere and matters enormously in the few places it does — anything measuring durations to sub-second accuracy across such an event should use a monotonic clock, not wall-clock timestamps. A monotonic clock also survives an NTP correction stepping the system clock backwards, which will otherwise produce negative durations.

The 2038 problem

A signed 32-bit integer overflows at 03:14:07 UTC on 19 January 2038, wrapping to 1901. Modern systems use 64-bit time and are fine for about 292 billion years, but 32-bit values persist in embedded devices, old database columns and file formats.

It is not a distant problem in one respect: any system today calculating a date more than a decade ahead — a mortgage, a long-dated instrument, a retention policy — is already crossing the boundary.

A timestamp is not a time

1700000000 is a single instant, and what a person calls that instant depends entirely on where they are. Store the timestamp, and render it in a time zone at the moment of display.

The exception is future local events. "09:00 on 3 March next year in Berlin" cannot be stored correctly as a timestamp, because if Germany changes its daylight-saving rules between now and then — governments do this, sometimes at short notice — the instant you computed is now the wrong local time. Future local appointments need the local time plus the zone identifier, resolved when displayed.

Practical rules

• Store instants as UTC timestamps or as UTC datetimes. Never as local time without a zone.
• Keep the unit in the field name — created_at_ms costs nothing and prevents the whole class of bug.
• Use a monotonic clock for durations, wall-clock time for instants.
• Store future local events as local time plus an IANA zone name, not as a computed timestamp.
• Treat any timestamp of exactly 0 with suspicion — it is far more often an uninitialised field than a real 1970 date.

Convert timestamps → · Trading-day calculator → · Back to all articles