Top
Best
New

Posted by sprawl_ 1 day ago

British Columbia, Time Zones, and Postgres(www.crunchydata.com)
137 points | 110 commentspage 2
_whiteCaps_ 1 day ago|
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.

empressplay 1 day ago|
Don't worry, WA, OR and CA probably aren't far behind.
StayTrue 1 day ago||
Dumb change on the part of British Columbia.

Source: me, BC resident.

charles_f 23 hours ago||
That law, voted years ago, has overwhelming public support (including mine). Source: an actual source^1

^1: https://news.gov.bc.ca/releases/2019PREM0103-001748#:~:text=...

patmcc 19 hours ago||
This is exactly what I mention in a sibling comment - an incredibly misleading survey is quoted here to make it look like there was overwhelming support.

>>>More than 93% of the record 223,273 British Columbians who completed the Province’s survey on time observance have indicated they would prefer a move to permanent daylight saving time (DST).

Sticking with Standard Time was not an option listed in that survey.

charles_f 11 hours ago||
Ah, you mean picking pst as the permanent timezone?
patmcc 8 hours ago||
Yeah; you can see some of the rationale here - https://savestandardtime.com/ (not affiliated, and they're a little wacky about it, but I broadly agree) - but the best argument is it seems to be better for sleep.
vojtapol 1 day ago||
Why?
patmcc 1 day ago|||
No 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 1 day ago|||
Correct and complete explanation.
empressplay 1 day ago||||
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.

KyleSanderson 1 day ago||
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.
fouc 22 hours ago||
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?

subarctic 23 hours ago|||
Sounds like Brexit #2
netsharc 18 hours ago|||
That reminds me about a nitpicky argument around Brexit, they were set to leave at midnight February 1, 2020, but... in which timezone? Brussels time, or UK time?

The EU won and the UK left at 2020-01-31, 23:00 GMT (local time)...

patmcc 19 hours ago|||
Thankfully much smaller potatoes, but yah, I'm annoyed by it
emptybits 1 day ago|||
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 farther away. Life goes on...

vanjoe 21 hours ago||
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.
xp84 1 day ago||
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.
tadfisher 1 day ago|
Apparently 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 1 day ago||
Isn'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.
eduction 21 hours 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 7 hours ago|
As the author of this blog post, your comment is overwhelmingly my favorite.
pphysch 1 day ago||
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.
dqv 23 hours 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/...

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 9 hours ago||
These 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.
dqv 6 hours ago||
"overengineering"

    UPDATE appointments
    SET starts_at_utc = local_time AT TIME ZONE timezone_name
    WHERE timezone_name = 'America/Vancouver'
      AND starts_at_utc > now();
pphysch 5 hours ago||
You elided the part where you are adding an extra column to your database just for this rare corner case.
mulmen 1 day ago||
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?
dqv 1 day ago|
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.

mulmen 21 hours ago||
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.
cozzyd 20 hours ago||
Indeed, 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 19 hours ago||
Specifically 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 10 hours ago||
right, but in principle postgres could keep track of this as a feature.
mulmen 35 minutes ago||
Right. That was my original suggestion.
acalvino4 1 day ago||
[dead]
thelonelyborg 1 day ago|
nice approach.