Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Time zones and calendars

Why is a local time a query against a mutable legal database, and why is date arithmetic not associative?

The constraint here is not physical and not logical. It is political. Civil time is defined by legislatures. It can change with a few weeks of notice. And changes can retroactively change the meaning of data that you stored long ago.

  • An instant is a physical quantity. It is stable.
  • A local time is a function of an instant and a mutable rules database (the time zone database).

binding (fixing a value early) of something defined by an outside authority that will change it. The advice is clear: store the instant plus the zone name, and apply the rules when you read. Storing a numeric instead of the name loses the information needed to reinterpret it later: "+05:30" does not tell you which zone it came from, so you cannot know what the rules would say for a future date.

Local time is neither total nor injective

  • At a fall-back boundary, one local time maps to two instants. In Germany (Europe/Berlin) on 26 October 2025 the clocks go from 03:00 back to 02:00, so local times from 02:00 to 02:59 occur twice.
  • At a spring-forward boundary, some local times do not exist. On 30 March 2025 the clocks jump from 02:00 to 03:00, so 02:30 never happens.

Any key, rule or ordering built on local time gets duplicates once a year and gaps once a year. This was Kavya's first symptom.

Calendar units are not durations

  • A month is 28 to 31 days.
  • A day is 23, 24 or 25 hours (on days with a clock change).
  • A year is 365 or 366 days.

So date arithmetic is not .

Different answers from the same two operations. (Conventions differ between libraries, but the order dependence does not.)

Two arithmetic systems live side by side: one on instants (seconds) and one on calendar fields (years, months, days). Nearly every calendar bug is a silent crossing from one to the other.

Leap seconds

A is an extra second added to from time to time, so that clocks follow the rotation of the Earth. It makes the physical clock non-monotone by policy. Large operators avoid the jump by : they spread the extra second over a long . During the smear their clocks intentionally disagree with UTC, and with other providers' smear windows, by up to about a second. So comparisons of sub-second times across providers are unreliable around the event. (This is current engineering reality with an expiry date: leap seconds are scheduled to be abandoned around 2035.)