British Columbia, Time Zones, and Postgres

jedberg

I would contend that you shouldn't store anything but current unix timestamps in UTC in your database. If you must store time in some other way, then the two column method in the post will work, but leave it up to your software library to do it. I prefer to leave all the time conversions to software, wherein you only use battle tested libraries, and never do it by hand. Timezones are just too fraught with peril to try and do it on your own. Edit: changed some words to make clearer what I was saying.

ncruces

This strategy fails for appointments during that hour where the clock goes back: they are ambiguous, can refer to two different moments in time. That caveat aside: good.

_whiteCaps_

I just need to know what happens to our 9am standups in Vancouver when the other team is in SF. If I'm doing the math correctly it moves to 10am. Also, I've often picked a random city in Pacific time when setting timezones on hosts, so I guess it's going to cause me some headaches in the fall.

jagged-chisel

Future events: store the local (at the event) date and time and timezone. You’ll keep the right context even if lawmakers decide to switch things up. You want to see your doctor at 8:30 AM on Monday September 14, 2026 whether it’s daylight saving time, or standard time or “they” decide on a fractional hour offset between the time you set the appointment and the time you attend the appointment. Past events: UTC timestamp. What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug. Note: nothing stops you from optimizing for queries by adding a field to store (or using a calculated index for) the integer epoch offset (e.g. unix timestamps), just make sure you know which field is authoritative.

ivan_gammel

ANSI SQL has DATE and TIME types. Just use them for appointments bound to location. Conversion to current user timezone must happen in presentation layer and certainly does not belong to a database.

rjrjrjrj

An added wrinkle is that parts of British Columbia use other timezones. The southeast corner follows Alberta time (previously MST/MDT but changing to MDT). Parts of the northeast and iirc a few other communities (eg Creston) have historically followed MST (no switch) and will now be effectively on the same time as Vancouver, albeit probably with a different TZ designation(?).

StayTrue

Dumb change on the part of British Columbia. Source: me, BC resident.

munk-a

This problem is not new and is a relatively minor exposure to the sort of issues that TZ conversion constantly needs to deal with. Different parts of the world have different dates that they adopt (or don't adopt) DST and some nations have changed this date in the past. Use a library, do not roll it yourself, do not try to outsmart tzdata... if you think you could then please volunteer for this project and either become a new Chronomancer[1] or get disabused of that misconception. 1. It's a legal title, people actually have to call you a Chronomancer if you've contributed to tzdata, it's the law.

xp84

I hope this gives us Americans the needed encouragement to do the same on the west US coast. Utter insanity to screw with the clocks twice a year instead of letting various institutions who have a compelling need, to publish "Summer hours" to suit them.

necro

Time is local Timestamp is a counter A unix timestamp does not have different timezones. It is a counter. No matter where u are in the world a timestamp call should give you the same numeric value at the same instant. It is not time zone adjusted. Store that number, unadjusted as the source of truth. You can get to any local time after that.

mulmen

The issue here seems to be that the behavior of tzdata (correctly) changes over time. Can all this complexity be avoided by storing the tzdata version in the timestamp itself so it can decoded with the same rules?

chaidhat

Whenever I see a tz post, I want to remind ya'll that the `tzdata` package is using data from `eggert/tz` by Paul Eggert -- a crazy good UCLA professor. I took his course once and in the exams, he'd like to put in a question he didn't know the answer to himself.

pphysch

This is one of those cases where I would prefer to be antifragile and rapidly "patch the data" once as opposed to trying to perfectly solve problems like this before they arise. In all likelihood this will never happen in a particular timezone.

thisrod

This problem isn't specific to timezones. In general, you did X on the basis that A was B, and now you know that that A is actually C. The strategies to handle that are quite interesting: https://martinfowler.com/articles/bitemporal-history.html

kelseydh

Lesser known is that western parts of British Columbia are still debating what timezone to adopt. E.g. the East Kootenays which used to align with Alberta's mountain time w/ daylight savings is now debating whether to align with B.C. or Alberta now that Alberta is also switching to permanent Mountain Daylight Time (which they call "Alberta Time").

eduction

> Going forward, the UTC offset for America/Vancouver timezone is permanently UTC-7. A rather bold use of the word “permanently” given that the province just changed the previous permanent setup.