The five time zone bugs that reach production
Almost every timezone bug is one of five mistakes. All five are avoidable with rules that fit in a paragraph.
1. Storing local time
A column holding 2026-03-29 01:30:00 with no zone is ambiguous, and on a spring-forward night it may describe an instant that never existed. Store UTC for anything that has already happened.
2. Storing an offset instead of a zone
+01:00 is a snapshot. It says nothing about what happens in July, and nothing about what happens if a government changes its rules. For future events, store the IANA identifier — Europe/London — alongside the local time.
3. Double conversion
The classic: a value is converted to the user's zone in the service layer, then again in the template. The result is offset by a fixed amount and looks entirely plausible to anyone in UTC. Convert once, at the display edge.
4. Assuming whole hours
Storing an offset as an integer number of hours works everywhere except India (UTC+5:30), Nepal (UTC+5:45), Iran, Afghanistan, Myanmar and the Chatham Islands. That is well over a billion people. Store minutes, or store the zone.
5. Never testing a transition
Most timezone bugs are invisible for 363 days a year. Write tests that run against the transition instants directly:
- A spring-forward gap, where 1:30am does not exist.
- An autumn fall-back, where 1:30am happens twice.
- A half-hour zone such as
Asia/Kolkata. - A zone with no daylight saving, such as
Asia/Tokyo.
The postcard version
Store UTC for the past, zone plus local time for the future, transmit ISO 8601, convert once at the edge, and test the two weekends a year when it all goes wrong.
A test suite that catches all five
Most timezone bugs are invisible for 363 days a year, so they have to be tested deliberately. A minimal suite covers four zones and two instants:
America/New_York— daylight saving on the US schedule.Europe/London— daylight saving on the EU schedule, three weeks apart from the US.Asia/Kolkata— a half-hour offset with no daylight saving.UTC— the control.
Against those, assert behaviour at the spring-forward gap (a local time that does not exist), the autumn fall-back (a local time that happens twice), and an ordinary midday.
The database layer
| Choice | Consequence |
|---|---|
TIMESTAMP in MySQL | Stored as UTC, converted by session zone — set the session explicitly |
DATETIME in MySQL | No zone at all; you must store one alongside it |
timestamptz in Postgres | Stores an instant; the sane default for past events |
| Unix integer | Unambiguous but unreadable, and useless for future events |
Future events deserve special care
A meeting booked for 09:00 on a date eighteen months away is not an instant yet — it becomes one only when the rules for that date are known. Store the local time plus Europe/London, resolve at render time, and a government rule change is handled for free. Store UTC and the meeting silently moves.
The review checklist
- Does every stored timestamp have a zone or an explicit UTC contract?
- Is conversion happening exactly once, at the display edge?
- Are offsets stored in minutes rather than hours?
- Do the tests include a transition instant?
Filed under Technical · Tags: software, bugs, iso 8601, database. Figures verified against the official definitions — see our editorial policy.