British Columbia, Time Zones, and Postgres

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.

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.

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(?).

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.

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.

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.

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.

Dumb change on the part of British Columbia.

Source: me, BC resident.

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.

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.

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.

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.

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?

Great post! I've thought about this issue a bit, and this is my take at solving it at the db level: https://github.com/acalvino4/pg_temporal

Once I decide on the best way to publish releases and cleanup the docs, I'll put out a ShowHN post, but the current version seems to be working for the basic use cases, and people are welcome to try it out if you don't mind a the work of cloning the repo and running the relevant commands!