Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

British Columbia, Time Zones, and Postgres

crunchydata.com

Post reply on HN
Print view View original

81–90 of 117 posts

Re: British Columbia, Time Zones, and Postgres

#81

Post by fouc »

KyleSanderson wrote: post #63
empressplay wrote: post #57

Earlier quoted context omitted.

If 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.

Go 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.

I always get up with the light so I think 4am would be too unreasonable. Especially if most cafes remain closed for hours still. 5am is tolerable.

As for winter, an extra hour of dark doesn't feel like a huge deal. When I commuted in the winter it was often happening around 6am while still dark anyways.

I have a feeling people's morning habits will change a bit to compensate for the darker mornings though. So I think it'll work out in the end.

Where do you expect the premature deaths to come from? Rush hour traffic in the dark?

Re: British Columbia, Time Zones, and Postgres

#82

Post by XYen0n »

ppchain wrote: post #5
jedberg wrote: post #3

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 tr…

The 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.

How does your system know when it is 2pm in British Columbia?

Re: British Columbia, Time Zones, and Postgres

#83

Post by vanjoe »

emptybits wrote: post #55
vojtapol wrote: post #37

Earlier quoted context omitted.

Why?

BC resident. I do look forward to not adjusting to a twice-annual time change. BUT, I would have strongly preferred sticking to Standard Time, year round, instead of DST. That option was apparently not on the table, and strangely got little mention or investigation by media. So a daily event approximating High Noon is literally gone forever here. Yes yes, depending on longitude, it was never there, but now it's even…

Also from BC: we get up in the dark in the winter either way. May as well have it light when I get off work.

Re: British Columbia, Time Zones, and Postgres

#84

Post by Insimwytim »

mgaunard wrote: post #14
ngaheer wrote: post #8

Earlier quoted context omitted.

"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 timezo…

Never 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).

DST works fine with Unix timestamps.

It's the scheduling changes that disrupt DST switch (or something else) need adjusting to. But this is usually planned in advance and everyone would (or at least should...) update their tzdata.

The amount of issues you'll have due to those (comparatively rare) changes cannot even begin to compare to the amount issues you'll have with datetime stored in timezones.

Re: British Columbia, Time Zones, and Postgres

#85

Post by Ndymium »

jagged-chisel wrote: post #19

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 timest…

Copying what I posted under the original[0] that no one noticed because it's quite relevant to your mention of UTC for past events:

The naming of "timestamp with time zone" is one of my favorite pet peeves. It's one of those things that you can say "well technically it's true" about.

The article suggests that for past events, UTC and this timestamptz would be acceptable as a general rule, but even there it depends on what you will be doing with the data. If you intend to interpret it as a series of local occurrences and try to visualize/summarize that data later, you may be in for a surprise as your user has moved to another timezone and now all the past events are translated to the wrong local hours [1]. For example, your system might end up showing that the user's best time for jogging based on historical data is at 2 in the night.

[0] https://news.ycombinator.com/item?id=48558005

[1] https://blog.nytsoi.net/2022/03/13/utc/

Re: British Columbia, Time Zones, and Postgres

#86

Post by eduction »

miki123211 wrote: post #80
jagged-chisel wrote: post #19

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 timest…

However, keep in mind that there is *no way* to store the time of a future event in a way that won't someday break unexpectedly. It just physically can't be done. Your approach assumes that we know what timezone the doctor's office will be in when the event happens. However, unless you know the exact lat/lon of that office — and maybe not even then — that's not something you can rely on. Countries sometimes split the…

Everything in the future is provisional and uncertain. The doctor could die, humanity could get obliterated, the database could go offline and you lose all your appointments.

Should we all add precise GPS coordinates to our salon appointments in case that neighborhood is seized by commandos from Newfoundland who really really want us all on GMT? I’m personally not sure it’s worth the effort.

Re: British Columbia, Time Zones, and Postgres

#87

Post by 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").

Re: British Columbia, Time Zones, and Postgres

#88

Post by mulmen »

dqv wrote: post #70
mulmen wrote: post #43

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?

It'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.

Yeah 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.

Re: British Columbia, Time Zones, and Postgres

#90

Post by kelseydh »

rjrjrjrj wrote: post #29

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

The 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/afcea8761543ac97aeee6399...

https://www.cbc.ca/news/canada/british-columbia/what-time-is...

Post reply on HN