Unix timestamps, UTC and the bugs in between

Seconds or milliseconds: telling them apart

A Unix timestamp counts from 1 January 1970 UTC. Whether it counts seconds or milliseconds is not part of the value, and the two are routinely mixed in the same system — Unix tools and most APIs use seconds, JavaScript and Java use milliseconds.

Get it wrong in one direction and you land in 1970; get it wrong in the other and you land tens of thousands of years in the future. Both are obvious in a UI and easy to miss in a comparison, a sort or a retention job that silently matches nothing.

The magnitudes are distinct enough to guess reliably:

Heuristics are for diagnosis, not for code. In code, name the unit in the field itself — expires_at_ms rather than expires — so the ambiguity cannot survive a code review. The Unix Timestamp Converter infers the unit from the magnitude and shows which one it picked, which is usually enough to settle the question.

The 2038 problem, and who still has it

A signed 32-bit integer counting seconds overflows at 03:14:07 UTC on 19 January 2038. One second later it wraps to December 1901.

Most modern systems moved to 64-bit time long ago, and it is tempting to treat this as solved. It is not solved everywhere:

• MySQL's TIMESTAMP column type is bounded by 2038. DATETIME is not — this is a schema decision, not a platform one, and it is being made wrongly today.
• Embedded systems and long-lived firmware.
• Binary formats and protocols with a fixed 32-bit time field.
• Any code that stores a timestamp in a 32-bit column or a 32-bit language type.

The dates that break first are future ones: a mortgage maturity, a 30-year certificate, a retention deadline. Those are being calculated now, so the failures start well before 2038.

An offset is not a time zone

This is the distinction that causes the most subtle date bugs, and it is worth being precise about.

An offset is a fixed difference from UTC: +05:30, -08:00. It describes one moment.

A time zone is a named region with a set of rules that determine the offset at any given moment: Asia/Kolkata, America/Los_Angeles. The offset varies through the year and the rules change over time as governments legislate.

The failure looks like this. A user in Los Angeles schedules a recurring meeting for 09:00. You store 2026-03-01T09:00:00-08:00. Daylight saving arrives, Los Angeles moves to -07:00, and your stored offset keeps resolving to 10:00 local. The value is not wrong for the instant it recorded; it is wrong for every instant after it, because it captured a symptom of the rules rather than the rules.

The correct storage for a future local event is the local time plus the zone name — 2026-03-01T09:00 with America/Los_Angeles — and the instant is computed when needed. Note that this also means the instant genuinely changes if a government alters the rules, which is the correct behaviour: the user wants 09:00 their time.

Because zone rules change, the IANA time zone database is updated several times a year. A system that pins an old copy will quietly compute wrong local times for affected regions, and the symptom is an hour's error in one country.

Local times that do not exist, and ones that happen twice

Daylight saving transitions break two assumptions that feel too basic to question.

Some local times do not exist. When clocks jump forward, an hour is skipped. In a zone that springs forward at 02:00, the time 02:30 simply never happens on that date. Parsing it either throws or silently shifts, depending on the library, and a daily job scheduled at 02:30 does not run.

Some local times happen twice. When clocks go back, an hour repeats. 01:30 occurs once at the old offset and once at the new one. A local timestamp in that window is genuinely ambiguous, and a job scheduled then runs twice. If it is a billing job, it charges twice.

Neither case is rare or regional: it happens twice a year in every observing zone. The practical defences are to schedule recurring jobs in UTC where the transition does not exist, to avoid scheduling anything between 01:00 and 03:00 local, and to make jobs idempotent so a double run is harmless.

A related trap: a day is not always 24 hours. Adding 86,400 seconds to a local time crosses a DST boundary incorrectly. Use a date library's "add one day" operation, which applies calendar arithmetic in the zone.

What to store, by what you are recording

The rule "always store UTC" is good advice for the first row and actively wrong for the second and third. What it should say is: store the thing you actually mean, and store the zone when the zone is part of the meaning.

A short checklist

• Put the unit in the field name. _ms and _s cost nothing.
• Never use a 32-bit type for a timestamp, and check your MySQL TIMESTAMP columns.
• Store a zone name for future local times. An offset is not a zone.
• Keep the time zone database updated.
• Assume every local time is either impossible or ambiguous twice a year, and make scheduled work idempotent.
• Compare instants, never formatted strings. Formatting is for people.

The Date & Time Converter shows a value in UTC and in a chosen zone at the same time, which makes an offset-versus-zone mistake visible rather than theoretical.