7 ms·
British Columbia, Time Zones, and Postgres
- acalvino4 3mo ago[dead]
- thelonelyborg 3mo agonice approach.
- jedberg 3mo agoI 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.
- merb 3mo agoIn that case only storing utc did not work when you created a date in the future before you updated tzdata
- jedberg 3mo agoI edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
- phantom784 3mo agoHow would this solve the British Columbia issue as described in the article?
- eduction 3mo agoYour time library is not going to design your database schema for you.
- ppchain 3mo agoThe issue described in the post is an example of when you cannot just rely on Unix timestamps. Specifically it comes down to which date is authoritative. A appointment with your dentist at 2pm Pacific Time in December 2026 has changed Unix timestamps in British Columbia. The dentist doesn't care about the Unix timestamp, she cares about the wall clock local time when you arrive for the appointment.
- jedberg 3mo agoI edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
- ncruces 3mo agoThe post is for the otherworldly magician who wrote your library then.
- ngaheer 3mo ago"Which date is authoritative". I don't understand this. The consumer books in his/ her local time stamp i.e. 12 PM pacific. Gets stored as Epoch milliseconds (and is passed around as a data structure i.e. Date struct with UTC as the timezone) and the providers sees the time stamp 3 PM EST or 2 PM CST depending on it's timezone at runtime (interface the provider it works with). I don't understand why a specific timezone has to be "authoritative" here. What am I missing.
- mgaunard 3mo agoNever heard of DST? The authoritative time is constant in the local time zone, but needs to change in UTC twice a year. This is the exact reason people store time in local time zones. Also remember the date/time where DST switching occurs is entirely timezone-specific, and it's not necessarily the same pattern every year (as demonstrated with British Columbia).
- 3mo ago
- rini17 3mo agoIf you don't understand what the library is doing, and blindly put in local time without any consideration, you will get bitten someday. And all libraries use the same timezone database/logic anyway and run into same issues the author describes.
- ivan_gammel 3mo agoIt‘s a common mistake to store everything as UTC timestamps and shows lack of understanding of time domain. Local time exists and it is neither UTC or timezone-dependent. Doctor office opens at 8 a.m. regardless of whether it is DST or not. Appointments are made in local time. Store them in local time.
- remus 3mo ago> Appointments are made in local time. Store them in local time. You may just be illustrating a particular use case, but it is more ambiguous in the general case. For example if you have arranged a meeting with someone in another timezone then maintaing the local timezone could lead to a misalignment for one of the participants.
- ivan_gammel 3mo agoThis is ok. When future local user time offset is unpredictable, pinning meeting time to a certain location is a good strategy. If at some point meeting shifts for some user, well-designed calendar app could warn them and let reschedule. It still works better than pure UTC because it is predictable for at least some of the users.
- ncruces 3mo agoThis 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.
- rini17 3mo agoHumans would often fail such appointments too.
- jagged-chisel 3mo agoI have yet to find a technological solution to this social problem. Also, I have yet to encounter this problem. For personal events, I sleep during this time. For company events, we always avoid this time.
- mulmen 3mo agoYou're right that for the most part this is avoided by convention and scheduling time changes at quiet times of day. A bit contrived but consider you are a maintenance worker in a facility that uses isolated timekeeping devices. "Change the clock on the vault back one hour at 3:00am".
- ncruces 3mo agoI encountered it when I was design the scheduling back-office for a LED video wall a few years ago when those became economical for a shop to own and run 24/7. The customer probably never noticed if I even did it “correctly” or couldn't be bothered if I didn't, but I remember I was bothered by it: (1) ensuring continuity of programming during the gap when it jumps forward (2) solving the ambiguity when it went backwards. Because obviously they wanted to think in local time.
- rjrjrjrj 3mo agoI have, in the context of time series charts. Lots of back and forth with QA.
- rawling 3mo agoSounds like the quoted RFC would help here. Storing the offset would make it unambiguous which of the two moments was meant. Your business logic would have to figure out what to do when the offset no longer exists (honour the clock time or convert to the new timezone) or is nonsense. The geographical reference would help decide what to do if you're not in a single location.
- _whiteCaps_ 3mo agoI 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.
- empressplay 3mo agoDon't worry, WA, OR and CA probably aren't far behind.
- jagged-chisel 3mo agoFuture 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.
- Xirdus 3mo agoUTC for past events doesn't always work either. For example, historical employee punch-in times. UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
- Terr_ 3mo agoIf past timestamps (UTC or otherwise) are unreliable, then there is some kind of math-bug going on.
- dqv 3mo agoNot always a math bug. Sometimes a human bug. Tzdata can have errors (it's crowdsourced after all) that cause past UTC stamps to be incorrect because that incorrect tzdata was used at conversion time. And since most people aren't storing the tzdata version they're using with the stamp, it would be very difficult to make corrections without also corrupting other stamps. The bottom line is, if wall time is important, past or present, wall time needs to be stored. The only thing that can be guaranteed about a UTC timestamp is it's a UTC timestamp.
- __s 3mo agoSeems like for airtightness you'd store utc alongside utc of when timestamp was stored alongside timezone
- ivan_gammel 3mo agoANSI 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 3mo agoAn 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(?).
- kelseydh 3mo agoThe East Kootenays still hasn't decided what timezone to align with. They used to align with Alberta (w/ daylight savings switching) as well. Then B.C. announced their change and the East Kootenays announced they were aligning to Pacific Daylight Time, which meant for the East Kootenays only uniquely "falling back" an hour a final time this fall. Then the East Kootenays rescinded that decision, and since then, Alberta has announced permanent daylight time. Now they need to decide which to align with. The current plan is for the East Kootenays (America/Cranbrook) to "fall back" to PDT this fall, but IANA TZ database still hasn't made the update because of the uncertainty. IANA revert: https://github.com/eggert/tz/commit/afcea8761543ac97aeee6399b62763ffa0abfac4 https://github.com/eggert/tz/commit/afcea8761543ac97aeee6399... https://www.cbc.ca/news/canada/british-columbia/what-time-is-it-in-the-east-kootenay-debate-9.7132624 https://www.cbc.ca/news/canada/british-columbia/what-time-is...
- StayTrue 3mo agoDumb change on the part of British Columbia. Source: me, BC resident.
- vojtapol 3mo agoWhy?
- patmcc 3mo agoNo the OP, but I'm also in BC and dislike this change. The change was made (partly) based on a bullshit poll of residents, that asked basically "Hey which would you rather do, use year-round Daylight Savings Time or keep switching every six months?" - notably not including the option a lot of people wanted (and which is well-supported by a lot of research as the best option), which was "use year-round Standard Time". And then in a bunch of press conferences and answers to the public, they would say "oh, this is what people chose" from the poll.
- StayTrue 3mo agoCorrect and complete explanation.
- empressplay 3mo agoIf we did that the sun would be coming up at 4am right now in Revelstoke. What's the point of the sun being up at 4am? On the other hand, I don't like it getting dark at 3:30 in the afternoon in Vancouver around Christmas. I know it means it will be darker later in the morning but you wake up in the dark that time of year already anyways.
- KyleSanderson 3mo agoGo for a run, hit the ski hill, all before you start work. The sun runs your health (circadian rhythm). I'm in Vancouver and this is the dumbest change. We're going to pay for this for a decade in premature deaths, and we'll end up on standard time anyway.
- munk-a 3mo agoThis 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 3mo agoI 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.
- tadfisher 3mo agoApparently there is a Federal restriction: we can opt to eliminate DST, like Hawaii and Arizona, but we cannot unilaterally decide to adopt "permanent DST" as did British Columbia. So we'll have to figure out how to get both houses of Congress to pass a bill into law, and have the President sign it, without said institutions convincing the world that keeping your clocks 1 hour forward is woke propaganda and injects gay chemicals into tadpoles.
- novemp 3mo agoIsn't the simple option to just move time zones? If you're not allowed to swap to PDT year round, just change to MST instead.
- deleted 3mo ago[deleted]
- necro 3mo agoTime 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 3mo agoThe 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?
- dqv 3mo agoIt's just a different kind of complexity and one that requires having a library that can arbitrarily load different versions of tzdata. I've been thinking about it for a while though - a time zone conversion library that also accepts an additional "tzdata_version" argument.
- mulmen 3mo agoYeah that's exactly what I am thinking. It could be a wrapper. But I think it would be better to take the (UTC) timestamp of the time of insertion instead of the TZDATA version itself. Then the Postgres instance can handle what version of TZDATA it had at that time.
- cozzyd 3mo agoIndeed, all you need to handle this correctly is the insertion (or update) time and the historical tzdata database. In almost all schemas, you will have this...
- mulmen 3mo agoSpecifically you need the TZDATA history of the local Postgres instance. Is the TZDATA version persisted at Postgres start time? Is it possible to query this information without recording it in the schema manually?
- cozzyd 3mo agoright, but in principle postgres could keep track of this as a feature.
- chaidhat 3mo agoWhenever 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 3mo agoThis 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.
- dqv 3mo ago> In all likelihood this will never happen in a particular timezone. You sure about that? https://lists.iana.org/hyperkitty/list/tz-announce@iana.org/latest https://lists.iana.org/hyperkitty/list/tz-announce@iana.org/... 2026b - changes to future timestamps 2026a - changes to past and future timestamps 2025c - changes to past timestamps 2025b - changes to past timestamps 2025a - changes to future timestamps 2024b - changes to past timestamps 2024a - changes to future timestamps 2023d - changes to past and future timestamps 2023c - changes that changed future timestamps reverted 2023b - changes to future timestamps I definitely prefer the... fragile? approach for this problem
- pphysch 3mo agoThese aren't global timezone changes, these are changes to individual or small batches of timezones. If you are not scheduling future events in these timezones, they do not affect you. You are welcome to overengineer systems to possibly prevent potential future timezone-shift-caused data corruption. Unless I ran a globally distributed appointment/event database, I would personally avoid doing that.
- thisrod 3mo agoThis 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 https://martinfowler.com/articles/bitemporal-history.html
- kelseydh 3mo agoLesser 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").
- userbinator 3mo agoWestern? Alberta is east of BC.
- kelseydh 3mo agoOops my bad, HN won't let me edit the comment. Yes I meant eastern parts of British Columbia.
- eduction 3mo ago> 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.
- winslett 3mo agoAs the author of this blog post, your comment is overwhelmingly my favorite.