Unix timestamps: seconds, milliseconds, and the UTC trap

A Unix timestamp is the number of seconds since 00:00:00 UTC on 1 January 1970. That is the whole definition. It has no time zone, no daylight saving, no locale and no ambiguity about which number comes first — which is exactly why it is the format almost every system stores time in.

The problems start at the boundaries: when a timestamp crosses between systems that disagree about its unit, and when it is converted to or from a human-readable date.

Seconds or milliseconds? Count the digits

The specification says seconds. A great deal of software uses milliseconds anyway, because that is what its language hands out. Date.now() in JavaScript, System.currentTimeMillis() in Java and time.time_ns() in Python all return something other than whole seconds, and the value gets stored as-is.

You can tell them apart by length, because the epoch has been running long enough that each unit has a distinctive size:

• 10 digits — seconds. 1758369600 is 20 September 2025.
• 13 digits — milliseconds. 1758369600000 is the same instant.
• 16 digits — microseconds. Common in databases, notably PostgreSQL internals.
• 19 digits — nanoseconds. Go, Prometheus and anything built on time.UnixNano().

The second failure is the more expensive one, because it is silent. A token whose expiry is set from Date.now() instead of Date.now() / 1000 does not error — it just never expires, and nobody notices until an audit asks why sessions from last March still work.

Large timestamps stop being exact

A JavaScript number is a double, which is exact only up to 9,007,199,254,740,991. That is 16 digits. A microsecond timestamp has 16 and a nanosecond timestamp has 19, so converting one with ordinary arithmetic starts from a number that is already not the one you were given.

Number('1758369600123456789')
// 1758369600123456800  — the last two digits are invented

BigInt('1758369600123456789')
// 1758369600123456789n — exact

For display purposes the error is sub-millisecond and harmless. For an identifier built from a timestamp — a Snowflake id, an event key — it is corruption, and it happens without any warning at all.

The UTC trap in date strings

This one catches almost everybody, because the behaviour is inconsistent within a single function. In JavaScript:

new Date('2026-09-20')            // parsed as UTC midnight
new Date('2026-09-20T10:00:00')   // parsed as LOCAL time
new Date('2026-09-20T10:00:00Z')  // parsed as UTC

A date-only string is UTC. Add a time and no offset, and it becomes local. This is not a quirk of one engine — it is what the ECMAScript specification says, and it means the same string represents different instants on a laptop in Mumbai and a server in Frankfurt. A difference of five and a half hours is enough to move a record to the previous day.

Dates that do not exist do not fail

Date parsing in most languages does not reject an impossible day — it rolls it forward. Date.parse("2026-02-29") does not return NaN; 2026 is not a leap year, so it quietly returns 1 March. 2026-04-31 becomes 1 May.

That is worse than an error, because the code carries on and answers a question about a different date than the one it was asked. Date arithmetic that adds a year to 29 February 2024 produces exactly this, and the result is off by a day for the rest of its life.

What about 2038?

Systems storing the timestamp in a signed 32-bit integer overflow on 19 January 2038 at 03:14:07 UTC and wrap round to 1901. This is a real problem in embedded systems, old C code and some database columns, and a non-problem anywhere the value is a 64-bit integer — which includes every modern language runtime.

It is worth checking the column type rather than the language. A TIMESTAMP in older MySQL is 32-bit; DATETIME and BIGINT are not.

Unix time ignores leap seconds

A Unix timestamp is defined as the number of seconds since the epoch *assuming every day has exactly 86,400 seconds*. Actual astronomical time does not co-operate: leap seconds have been inserted 27 times since 1972 to keep clocks aligned with the Earth's rotation.

Unix time handles this by pretending it did not happen. During a leap second, the timestamp either repeats a value or is smeared across the surrounding hours, depending on how the machine is configured. Google, Amazon and Cloudflare all smear; a plain NTP setup repeats.

For almost all software this is invisible and irrelevant. It matters in two places: code that assumes a timestamp is strictly increasing — which it is not, on a repeating machine — and code that measures elapsed time by subtracting two wall-clock readings. For durations, use a monotonic clock (performance.now(), CLOCK_MONOTONIC, time.monotonic()) rather than the wall clock, which also protects you from a user changing the system time mid-measurement.

What to store in a database

Three reasonable options, and one that causes trouble:

• `TIMESTAMPTZ` in Postgres — stores an instant in UTC and converts on read. The usual right answer.
• A 64-bit integer of epoch milliseconds — unambiguous, portable, and immune to any driver deciding to be helpful about time zones.
• An ISO 8601 string with an offset — readable in a dump, sortable as text, slightly larger.
• A naive `DATETIME` with no zone — this is the one to avoid. It records a wall-clock reading with no record of where the clock was, and the information needed to interpret it is gone for good.

One genuine exception: a future appointment in a specific place is better stored as a local time plus a time zone name, not as an instant. If a government moves a daylight-saving boundary — which happens most years somewhere — a meeting stored as an instant silently shifts by an hour, while one stored as "09:00 in Europe/London" stays at nine.