[jedberg]

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 try and do it on your own.

Edit: changed some words to make clearer what I was saying.

[ppchain]

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.

[Terr_]

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.

[jedberg]

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.

[ncruces]

The post is for the otherworldly magician who wrote your library then.

[ngaheer]

"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 timezone has to be "authoritative" here. What am I missing.

[Terr_]

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.

[Xirdus]

The appointment is for 12/1/26 2PM of whatever timezone the office is in at the time of the appointment.

Between the time of booking and the time of the appointment, the government changed what timezone there will be at the time of the appointment. If you calculated the Unix timestamp at the time of booking using the old laws, by the time of the appointment the Unix timestamp would be off by an hour.

[Mogzol]

You have to choose one timezone to be authoritative here, which one you choose depends on the situation, but if you don't choose one, then a change like the one happening in BC will cause the consumer and provider to be using mismatched times.

If the consumer booked something for 12PM Dec 1 2026 PST, and this booking was made before BC's changes were announced, and the provider saved this using their local time (say EST), then they would have saved it as 3PM EST. But now with BC's changes, when the consumer shows up at 12PM their time, that will be 2PM EST, an hour before the provider was expecting.

Was the consumer correct for showing up at the booked time according to their wall clock, or is the provider correct with their saved the time that was an hour later? The answer probably depends, but whichever you choose is the authoritative time.

[mgaunard]

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

[em-bee]

you booked an appointment in the future. before the day of your appointment arrives, british columbia changes the timezone. all appointments after that change now must follow the new timezone rules. therefore the time of your appointment relative to UTC is now different.

[slyall]

Except that the timezone in British Columbia has now changed. Lets say you stored the appointment for November in UTC.

So you set the time to 10pm UTC to match 2pm British Columbia. But because the timezone for BC has changed that 10pm now matches 3pm in British Columbia

So the Dentist is expecting you at 2pm and you come along at 3pm

[ivan_gammel]

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.

[remus]

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

[merb]

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

[jedberg]

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.

[phantom784]

How would this solve the British Columbia issue as described in the article?

[rini17]

If you don't understand what the library is doing, and blindly put in local time without any consideration, you will get bitten someday. And all libraries use the same timezone database/logic anyway and run into same issues the author describes.