11–20 of 117 posts
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.
[dead]
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.
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…
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).
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…
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
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…
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.
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…
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.
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…
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.