British Columbia, Time Zones, and Postgres

On March 8, 2026, British Columbia moved their clocks to a year-round Pacific Daylight Savings Time. In March, they did the spring forward one hour with their clocks to UTC-7, but they won’t fall back to UTC-8 in November. Going forward, the UTC offset for America/Vancouver timezone is permanently UTC-7.

Let’s use this as an opportunity to talk about date and time zone storage. In the most basic examples, the default is to store the UTC value, then calculate local time relative to UTC. However, people using calendar systems think in terms of local time (i.e. wall clock time), and never consider UTC. After modifying time zone data, these time calculations from UTC for a region will differ from the user’s input value.

If you stored timestamps in a UTC-based column for British Columbia-based appointment in 2026 and beyond, your November through March appointments may be off by an hour!

…

A fascinating read into a problem that I knew existed… and fear!

This is the kind of thing that keeps me up at night every time I expand on Three Rings‘ timezone support. Right now, Three Rings stores the times of “shifts” (when volunteers do things) as UTC timestamps but treats them as “local time” to the voluntary organisation using them. If they say a shift starts at 8pm, that’s probably what they mean: they mean 8pm local time, no matter where they happen to be in the world and whether or not their region uses daylight saving time.

But this approach, while simple, has limitations:

1. Three Rings doesn’t correctly respect shift duration variance as a result of daylight saving time clock changes. Suppose a Samaritans branch has a four-hour shift starting at midnight on Saturday night/Sunday morning. Twice a year, that shift will intersect with the clocks going forwards or backwards. Is it still a “four hour” shift? Well that depends: does the volunteer stay for four hours regardless of the clock change, or do they always go home at 4am? Three Rings assumes the former because it’s mathematically simpler, but in reality the latter is probably true (and the volunteer actually worked three or five hours).
2. Three Rings isn’t suited to organisations that span multiple timezones. When an Australian charity started using Three Rings they were interested in getting an overview of how many of their helplines – which are spread across three to four timezones (depending on whether daylight saving is in effect: half of central Australia observes daylight savings and half doesn’t!) – are open at any given time. Three Rings currently isn’t designed in a way to make it easy to answer that question.
3. Three Rings makes some suboptimal choices if you’re a long way from the Prime Meridian. This one’s easier to fix, and we probably will, but if you’re far from the UK and especially if you’re West of the UK then our scheduled tasks to e.g. “lock” shifts that appear to have “started” and prevent their easy modification… triggers at the wrong time.

Items 1 and 2 on that list are hard problems to solve, especially starting from where we are with our vast corpus of 24 years worth of “assumed local” time data. We’re improving on this kind of thing by increments, but it takes a while…

…and then, just sometimes, some country decides to fiddle with its timezone or its observance of daylight saving time and makes programmers’ lives hell.

Still; I’ll be referring to this blog post by Christopher Winslett the next time I’m working on a big set of timezone-related enhancements for Three Rings, for sure!