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

51–60 of 117 posts

Re: British Columbia, Time Zones, and Postgres

#51

Post by lalaithion »

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…

What about virtual events between participants in different time zones? Whose do you keep stable if one has their clock moved under them?

Re: British Columbia, Time Zones, and Postgres

#52

Post by dqv »

drdexebtjl wrote: post #48
dqv wrote: post #42

Earlier quoted context omitted.

Not 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, p…

When was the last time tzdata was wrong about a period that already passed? Most of my career I’ve seen problems where it’s out of date, never where it’s up to date and wrong.

For the 2026a release:

    Changes to past and future timestamps

    Since 2022 Moldova has observed EU transition times, that is, it has sprung forward at 03:00, not 02:00, and has fallen back at 04:00, not 03:00.  (Thanks to Heitor David Pinto.)

Re: British Columbia, Time Zones, and Postgres

#53

Post by tadfisher »

xp84 wrote: post #33

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.

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.

Re: British Columbia, Time Zones, and Postgres

#54

Post by Xirdus »

Terr_ wrote: post #36
Xirdus wrote: post #26

Earlier quoted context omitted.

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

If past timestamps (UTC or otherwise) are unreliable, then there is some kind of math-bug going on.

"UTC or otherwise" is important. Are you storing otherwise, or are you storing UTC? There are times where storing otherwise will lead to data loss in case the law changes whereas storing UTC would work (when you care about a literal point in time, like access logs). And there are times where storing UTC will lead to data loss in case the law changes whereas storing otherwise would work (when you care about wall clock time, like punch-in times).

Re: British Columbia, Time Zones, and Postgres

#55

Post by emptybits »

vojtapol wrote: post #37
StayTrue wrote: post #30

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

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 farther away. Life goes on...

Re: British Columbia, Time Zones, and Postgres

#56

Post by drdexebtjl »

Xirdus wrote: post #49
drdexebtjl wrote: post #39

Earlier quoted context omitted.

Why not? It sounds like it would be correct even if the employee has a shift that includes a offset change. Future timestamps should be local because local timezone changes literally change the instant the event it will happen (relative to UTC). For past things, this can’t happen

Correct according to what? An employee who punched in at 9AM wouldn't show up as having punched in at 9AM anymore. Not unless you also store the exact timezone the UTC timestamps have been created with - but that's basically local timestamps with extra steps.

tzdata doesn’t change retroactively [1].

If an employee clocks in at 2026-06-22 09:00 America/Sao_Paulo time, (which has a -03:00 offset today), and the server's clock is in UTC, the server will save 2026-06-22 12:00 to the database.

If America/Sao_Paulo changes to -02:00 on 2027, it doesn’t affect conversions for past dates. You still get 2026-06-22 09:00 when trying to convert 2026-06-22 12:00 to local time in America/Sao_Paulo.

edit: [1] unless it was wrong. in which case, you actually still want the UTC timestamp stored, so that you can just update tzdata and get correct local times, as opposed to saving the wrong local times in your database, that you now have to also fix.

Re: British Columbia, Time Zones, and Postgres

#57

Post by empressplay »

patmcc wrote: post #47
vojtapol wrote: post #37

Earlier quoted context omitted.

Why?

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

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.

Re: British Columbia, Time Zones, and Postgres

#58

Post by empressplay »

_whiteCaps_ wrote: post #11

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.

Don't worry, WA, OR and CA probably aren't far behind.

Re: British Columbia, Time Zones, and Postgres

#59

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

Re: British Columbia, Time Zones, and Postgres

#60

Post by drdexebtjl »

dqv wrote: post #52
drdexebtjl wrote: post #48

Earlier quoted context omitted.

When was the last time tzdata was wrong about a period that already passed? Most of my career I’ve seen problems where it’s out of date, never where it’s up to date and wrong.

For the 2026a release: Changes to past and future timestamps Since 2022 Moldova has observed EU transition times, that is, it has sprung forward at 03:00, not 02:00, and has fallen back at 04:00, not 03:00. (Thanks to Heitor David Pinto.)

Interesting. I could see that as an argument also for storing it in UTC, no?

For example, if tzdata is 1 hour off, and you store your timestamps in UTC, it's immediately obvious that a local time is wrong because users will see events that just happened as having happened 1 hour ago. Update tzdata, and now everything is right.

If you store the wall time, it _looks_ right, but fails if you attempt to compare/sort it with times in different timezones. To fix it, you need to actually modify the data in your database.

Post reply on HN