The bug that arrives on a Sunday
A scheduled report is meant to run at midnight. For most of the year it does. Then a customer in London reports that on one Sunday in March it never arrived, and on one in October it arrived twice. Nothing in the code changed. The clocks did.
Time is the part of software that feels simple until the first real user in a different place touches it. Nigeria and Ghana do not observe daylight saving, which can lull a team into thinking the problem does not apply. It does the moment you serve the UK, a UK bank, or a cloud region elsewhere.
Store instants in UTC
The foundation is to separate two ideas. An instant is a precise moment in the universe: the payment was received at this exact point. A local time is what a wall clock would have said somewhere. These are not interchangeable.
Store instants in UTC, in a column type that understands time zones. In PostgreSQL that means timestamptz, not timestamp. Despite its name, timestamptz does not store a zone. It converts input to UTC and stores that, so the stored value is unambiguous. The plain timestamp type stores whatever you hand it with no context, and every reader has to guess.
Convert to a local zone only at the edge, when showing a person a time. Never do arithmetic on strings that look like times.
Store the zone name separately
UTC alone is not enough, because sometimes you need to know where a time was meant. A clinic appointment at 9:00 on a given day in London is not a fixed instant until you know the rules in force for London on that date.
So for anything scheduled by a human, store the local date and time plus the IANA zone name, such as Europe/London or Africa/Lagos. Never store a fixed offset like +01:00 as a stand in. Offsets change with daylight saving. Zone names carry the rules.
Where it breaks
Recurring schedules. "Every day at 02:30" has no valid 02:30 on the day clocks spring forward in the UK, and two of them when clocks go back. Decide the behaviour ahead of time: skip, run at the next valid time, or run once. Write it down and test it.
Day boundaries. A "daily total" depends on whose day it is. A payment at 23:30 UTC is already tomorrow in Lagos during the winter if a user's zone is ahead of UTC. Reports, statements and limits need an explicit, agreed time zone, and customers should be able to see which one.
Durations versus calendar time. Adding 24 hours is not the same as adding one day. Across a clock change, a day can be 23 or 25 hours long. Use date arithmetic for calendar ideas such as "next month" and duration arithmetic for elapsed time, and do not mix them.
Month ends and leap years. One month after 31 January is a judgement call. Pick a rule and be consistent.
Server and database settings. A server set to local time, or a database session with a different zone from the application, turns timestamps into a puzzle. Run servers in UTC and set the zone explicitly where it matters.
Rules to adopt
- Use the language's proper date and time library. Do not write your own conversions or parse dates with string splitting.
- Keep the zone database current. The IANA time zone data changes when governments change rules, sometimes with little notice, so operating systems and runtimes need updates.
- Pass timestamps between systems in ISO 8601 with an explicit offset or
Z, never as an unlabelled string. - Decide, per feature, whose time zone applies: the user's, the account's, or the business's. Say so in the interface.
Test with awkward dates
Add test cases that matter: the hour that does not exist, the hour that happens twice, 31 January plus a month, a leap day, midnight exactly, and a user whose zone is far from the server's. Most time bugs come from a small set of dates, and a short list of tests catches nearly all of them.
The simplest habit
Whenever you write a time into storage, ask what it means: an instant, a local time, or a date with no time at all. Name it that way in the column, the field and the API. Most time zone bugs are really confusion about which of those three you were holding.