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

31–40 of 117 posts

Re: British Columbia, Time Zones, and Postgres

#31

Post by munk-a »

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

Re: British Columbia, Time Zones, and Postgres

#32

Post by Terr_ »

munk-a wrote: post #31

This 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 bec…

Indeed, recognizing Chronomancy will once have soon always been the case.

Re: British Columbia, Time Zones, and Postgres

#33

Post by xp84 »

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.

Re: British Columbia, Time Zones, and Postgres

#34

Post by remus »

ivan_gammel wrote: post #27
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…

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

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

Re: British Columbia, Time Zones, and Postgres

#36

Post by Terr_ »

Xirdus wrote: post #26
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…

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.

Re: British Columbia, Time Zones, and Postgres

#38

Post by Terr_ »

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.

To frame it another way, your dentist has agreed to a particular triggering condition. (Whether they realize that's how their country works or not.)

We can all make pretty-good guesses about when that condition will be fulfilled in N seconds, but until it actually finally happens, the true occasion could land somewhere else.

"Three months later at 2PM where I live" is not so different than "when the thrush knocks during the setting light of Durin's Day." You can guess that it's N seconds from now, but you might be wrong.

Re: British Columbia, Time Zones, and Postgres

#39

Post by drdexebtjl »

Xirdus wrote: post #26
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…

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.

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

Re: British Columbia, Time Zones, and Postgres

#40

Post by Terr_ »

ngaheer wrote: post #8
ppchain wrote: post #5

Earlier quoted context omitted.

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.

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

Clocks are social constructs, and your system needs to have some decision in it about whose clocks take precedence over other ones.

No matter which of these you choose, there's a possibility that someone says: "Hey! That's wrong! I never agreed to that":

1. The meeting shall be in precisely N seconds, no exceptions. (UTC, or something very close to it.)

2. The meeting shall be when participant A's wall-clock shows X.

3. The meeting shall be when participant B's wall-clock shows Y.

At the present instant, you might have predicted values so that X Y and N all land on the same spot, but the prediction is not reliable and the equality will collapse if anybody's government makes timezone changes. Or if their country is taken over by another. Or splits due to civil war.

Post reply on HN