Storing time: instants, wall clocks, and the difference

The standard advice is to store everything in UTC and convert on display. It is good advice for about eighty per cent of cases, and actively wrong for the rest — because there are two different things people call a time.

Instants

An instant is a moment on the universal timeline. When a payment settled, when a log line was written, when a user signed up. These have a single correct answer everywhere on Earth, and UTC — or a Unix timestamp, which is the same thing — is exactly right.

Wall-clock times

A wall-clock time is what a clock on a wall says. A recurring meeting at 09:00 in London. A shop that opens at 08:00. An alarm at 07:30. These are not instants: they are a local time plus a rule for turning it into one.

Store a recurring 09:00 London meeting as a UTC instant and it is correct until the clocks change, at which point it becomes an 08:00 or 10:00 meeting. The user did not mean a fixed point on the timeline. They meant nine o'clock.

Converting to UTC is lossy. It discards the zone the user was thinking in, and you cannot reconstruct it from the result.

What to store

• Past instants — a UTC timestamp. timestamptz in Postgres, which stores an instant and normalises on input.
• Future scheduled events — the local time plus the IANA zone name, separately. Compute the instant when you need it.
• Dates with no time — a plain date. A birthday is not midnight anywhere; treating it as an instant makes it shift a day for some users.
• Durations — a number of seconds, not two timestamps, when what you mean is elapsed time.

Zone names, not offsets

Store Europe/London, not +01:00. The offset is a consequence of the zone and the date; it changes twice a year and the rules themselves change when governments legislate. An IANA name survives those changes, an offset does not.

The awkward hours

On the spring transition, an hour does not exist — 01:30 simply never happens. On the autumn one, an hour happens twice, so 01:30 is ambiguous. Any library converting a local time to an instant has to make a decision about both, and different libraries decide differently. If you schedule jobs in local time, avoid the transition window entirely.

Testing

Most date bugs never appear in development because the developer's machine is in the same zone as the test data. Run the suite with TZ set to something awkward — Pacific/Chatham has a 45-minute offset, Australia/Lord_Howe changes by 30 minutes — and a surprising number of assumptions surface immediately.