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

91–100 of 117 posts

Re: British Columbia, Time Zones, and Postgres

#91

Post by mulmen »

jagged-chisel wrote: post #66
mulmen wrote: post #44

Earlier quoted context omitted.

> What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug. You can just use a TIMESTAMP with no TZ data. It's functionally the same as using the string but simpler because you avoid all the string handling headaches and gain the benefit of avoiding to avoid double booking and date/time functions to answer questions like "how many appointments do I ha…

I am willing to concede to “human readable” and dropping “string” iff queries on TIMESTAMP are producing a human readable string (I believe they are … I haven’t been in postgres in at least six weeks and details like that don’t make a lasting impression in muh brain)

Why would queries on TIMESTAMP not produce human readable strings? Postgres has defaulted to a human readable mask for timestamp presentation for at least 25 years.

See Current: https://www.postgresql.org/docs/current/datatype-datetime.ht...

And 7.1 (2001-04-13): https://www.postgresql.org/docs/7.1/datatype-datetime.html (Section 3.4.2)

Re: British Columbia, Time Zones, and Postgres

#92

Post by eduction »

jedberg wrote: post #7
merb wrote: post #4

Earlier quoted context omitted.

In that case only storing utc did not work when you created a date in the future before you updated tzdata

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

Your time library is not going to design your database schema for you.

Re: British Columbia, Time Zones, and Postgres

#93

Post by cozzyd »

mulmen wrote: post #88
dqv wrote: post #70

Earlier quoted context omitted.

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.

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

Re: British Columbia, Time Zones, and Postgres

#94

Post by patmcc »

charles_f wrote: post #76
StayTrue wrote: post #30

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

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

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.

Re: British Columbia, Time Zones, and Postgres

#95

Post by patmcc »

subarctic wrote: post #75
patmcc wrote: post #47

Earlier quoted context omitted.

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

Sounds like Brexit #2

Thankfully much smaller potatoes, but yah, I'm annoyed by it

Re: British Columbia, Time Zones, and Postgres

#96

Post by mulmen »

cozzyd wrote: post #93
mulmen wrote: post #88

Earlier quoted context omitted.

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.

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

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?

Re: British Columbia, Time Zones, and Postgres

#97

Post by userbinator »

kelseydh wrote: post #87

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

Western? Alberta is east of BC.

Re: British Columbia, Time Zones, and Postgres

#98

Post by netsharc »

eduction wrote: post #86
miki123211 wrote: post #80

Earlier quoted context omitted.

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.

Funnily enough, Google Maps' Timeline tells me I went to a different restaurant 5 years ago. Imagine if you went to a McDonald's in 2019 and in 2021 it became a KFC. Google Maps today will say "You visited this KFC in 2019"!.

Re: British Columbia, Time Zones, and Postgres

#99

Post by netsharc »

subarctic wrote: post #75
patmcc wrote: post #47

Earlier quoted context omitted.

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

Sounds like Brexit #2

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

Re: British Columbia, Time Zones, and Postgres

#100

Post by _glass »

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…

Always store the location, too. Space-Time is a thing.
Post reply on HN