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.
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.
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.
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.
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.
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!
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.
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 timezone has to be "authoritative" here. What am I missing.
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.
The post is about the otherworldly magician who wrote your library then.
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.
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.
Humans would often fail such appointments too.
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.
Great post! I've thought about this issue a bit, and this is my take at solving it at the db level: https://github.com/acalvino4/pg_temporal
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!
nice approach.