Rendered at 23:13:31 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
jagged-chisel 1 hours ago [-]
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 timestamp.
What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
Note: nothing stops you from optimizing for queries by adding a field to store (or using a calculated index for) the integer epoch offset (e.g. unix timestamps), just make sure you know which field is authoritative.
Xirdus 1 hours ago [-]
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.
drdexebtjl 38 minutes ago [-]
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
Terr_ 45 minutes ago [-]
If past timestamps (UTC or otherwise) are unreliable, then there is some kind of math-bug going on.
dqv 11 minutes ago [-]
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, past or present, wall time needs to be stored.
The only thing that can be guaranteed about a UTC timestamp is it's a UTC timestamp.
munk-a 55 minutes ago [-]
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.
Terr_ 51 minutes ago [-]
Indeed, recognizing Chronomancy will once have soon always been the case.
necro 21 minutes ago [-]
Time is local
Timestamp is a counter
A unix timestamp does not have different timezones. It is a counter.
No matter where u are in the world a timestamp call should give you the same numeric value at the same instant. It is not time zone adjusted. Store that number, unadjusted as the source of truth.
You can get to any local time after that.
ivan_gammel 1 hours ago [-]
ANSI SQL has DATE and TIME types. Just use them for appointments bound to location. Conversion to current user timezone must happen in presentation layer and certainly does not belong to a database.
rjrjrjrj 56 minutes ago [-]
An added wrinkle is that parts of British Columbia use other timezones.
The southeast corner follows Alberta time (previously MST/MDT but changing to MDT).
Parts of the northeast and iirc a few other communities (eg Creston) have historically followed MST (no switch) and will now be effectively on the same time as Vancouver, albeit probably with a different TZ designation(?).
jedberg 2 hours ago [-]
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 2 hours ago [-]
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_ 38 minutes ago [-]
To frame it another way, your dentist has agreed to a particular triggering condition. We can all make pretty-good guesses about when that condition will be fulfilled, but until it actually happens, they are still guesses subject to revision.
"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 2 hours ago [-]
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 2 hours ago [-]
The post is for the otherworldly magician who wrote your library then.
ngaheer 2 hours ago [-]
"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_ 32 minutes ago [-]
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.
Mogzol 1 hours ago [-]
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.
Xirdus 1 hours ago [-]
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.
mgaunard 1 hours ago [-]
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 1 hours ago [-]
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 1 hours ago [-]
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 58 minutes ago [-]
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 47 minutes ago [-]
> 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 2 hours ago [-]
In that case only storing utc did not work when you created a date in the future before you updated tzdata
jedberg 2 hours ago [-]
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 1 hours ago [-]
How would this solve the British Columbia issue as described in the article?
rini17 1 hours ago [-]
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.
Once I decide on the best way to publish releases and cleanup the docs, I'll put out a ShowHN post, but the current version seems to be working for the basic use cases, and people are welcome to try it out if you don't mind a the work of cloning the repo and running the relevant commands!
thelonelyborg 1 hours ago [-]
nice approach.
ncruces 2 hours ago [-]
This strategy fails for appointments during that hour where the clock goes back: they are ambiguous, can refer to two different moments in time.
That caveat aside: good.
jagged-chisel 1 hours ago [-]
I have yet to find a technological solution to this social problem.
Also, I have yet to encounter this problem. For personal events, I sleep during this time. For company events, we always avoid this time.
ncruces 58 minutes ago [-]
I encountered it when I was design the scheduling back-office for a LED video wall a few years ago when those became economical for a shop to own and run 24/7.
The customer probably never noticed if I even did it “correctly” or couldn't be bothered if I didn't, but I remember I was bothered by it: (1) ensuring continuity of programming during the gap when it jumps forward (2) solving the ambiguity when it went backwards.
Because obviously they wanted to think in local time.
mulmen 1 hours ago [-]
You're right that for the most part this is avoided by convention and scheduling time changes at quiet times of day.
A bit contrived but consider you are a maintenance worker in a facility that uses isolated timekeeping devices. "Change the clock on the vault back one hour at 3:00am".
rawling 1 hours ago [-]
Sounds like the quoted RFC would help here. Storing the offset would make it unambiguous which of the two moments was meant. Your business logic would have to figure out what to do when the offset no longer exists (honour the clock time or convert to the new timezone) or is nonsense. The geographical reference would help decide what to do if you're not in a single location.
rini17 1 hours ago [-]
Humans would often fail such appointments too.
_whiteCaps_ 1 hours 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.
xp84 49 minutes 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.
Past events: UTC timestamp.
What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
Note: nothing stops you from optimizing for queries by adding a field to store (or using a calculated index for) the integer epoch offset (e.g. unix timestamps), just make sure you know which field is authoritative.
UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
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
The bottom line is, if wall time is important, past or present, wall time needs to be stored.
The only thing that can be guaranteed about a UTC timestamp is it's a UTC timestamp.
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.
A unix timestamp does not have different timezones. It is a counter. No matter where u are in the world a timestamp call should give you the same numeric value at the same instant. It is not time zone adjusted. Store that number, unadjusted as the source of truth. You can get to any local time after that.
The southeast corner follows Alberta time (previously MST/MDT but changing to MDT).
Parts of the northeast and iirc a few other communities (eg Creston) have historically followed MST (no switch) and will now be effectively on the same time as Vancouver, albeit probably with a different TZ designation(?).
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.
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.
"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.
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.
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.
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.
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.
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).
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
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.
Once I decide on the best way to publish releases and cleanup the docs, I'll put out a ShowHN post, but the current version seems to be working for the basic use cases, and people are welcome to try it out if you don't mind a the work of cloning the repo and running the relevant commands!
That caveat aside: good.
Also, I have yet to encounter this problem. For personal events, I sleep during this time. For company events, we always avoid this time.
The customer probably never noticed if I even did it “correctly” or couldn't be bothered if I didn't, but I remember I was bothered by it: (1) ensuring continuity of programming during the gap when it jumps forward (2) solving the ambiguity when it went backwards.
Because obviously they wanted to think in local time.
A bit contrived but consider you are a maintenance worker in a facility that uses isolated timekeeping devices. "Change the clock on the vault back one hour at 3:00am".
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.
Source: me, BC resident.