Storing Times for Human Events(simonwillison.net)
simonwillison.net
Storing Times for Human Events
https://simonwillison.net/2024/Nov/27/storing-times-for-human-events/
81 comments
It would be nice if HN took advantage of the fast Algolia search as the person is typing in the "Submit URL Field" to show the last time(s) it has been submitted in chronological order to help make a better informed decision as to whether something is worth submitting in the first place.
That's on the roadmap, circa 2045.
We just got unpaginated threads after several decades so it'll be a while before the developer cooldown is up.
We just got unpaginated threads after several decades so it'll be a while before the developer cooldown is up.
> second chance pool maybe?
It appears to be so. You can hover your cursor over the relative time of the post to see an absolute date which does not change.
It appears to be so. You can hover your cursor over the relative time of the post to see an absolute date which does not change.
which is funny, given the topic.
Yeah, I think that's a bit weird too. Note that this is my article but none of those submissions were by me.
Maybe Hacker News needs to extend the time period during which it checks for duplicate URLs?
Maybe Hacker News needs to extend the time period during which it checks for duplicate URLs?
Reposting will continue until we can agree on a date format that lets us clearly and unambigously state when we'll stop it :)
But seriously, yes, I think this is a very reasonable observation that's still surprisingly controversial and accordingly could benefit from more boosting.
But seriously, yes, I think this is a very reasonable observation that's still surprisingly controversial and accordingly could benefit from more boosting.
I think one very common situation you are missing in "Things that can go wrong" is repeating events:
If an event repeats every Sunday at 8pm, people expect that to stay at 8pm local time. Which means you need to know the original timezone the event was created in and make sure the interval is counted in local time, respecting DST. A `timestamptz` field is not enough for that, you need the additional `timezone` storage.
Another thing that I think is useful to mention is the new extensions to ISO8601 time formatting that allow you to include the actual time zone specifier, which is now finally being standardized:
https://tc39.es/proposal-temporal/docs/strings.html
> Although neither ISO-8601 nor RFC 3339 specifications currently use this syntax, it's on a standards track led by the IETF SEDATE working group which includes ECMAScript Temporal champions as well as other industry participants.
It is very important to use a high quality time library that forces you to think about these things.
If an event repeats every Sunday at 8pm, people expect that to stay at 8pm local time. Which means you need to know the original timezone the event was created in and make sure the interval is counted in local time, respecting DST. A `timestamptz` field is not enough for that, you need the additional `timezone` storage.
Another thing that I think is useful to mention is the new extensions to ISO8601 time formatting that allow you to include the actual time zone specifier, which is now finally being standardized:
https://tc39.es/proposal-temporal/docs/strings.html
2007-12-03T10:15:30+01:00[Europe/Paris]
> Although neither ISO-8601 nor RFC 3339 specifications currently use this syntax, it's on a standards track led by the IETF SEDATE working group which includes ECMAScript Temporal champions as well as other industry participants.
It is very important to use a high quality time library that forces you to think about these things.
That's a nice extinction to ISO 8601.
One glaring omission from many tools for modeling chronology is that we have date, and datetime, but often don't have a type for just "time".
Sometimes you really just want to express that something is going to happen at a certain time, regardless of timezone. If I set my alarm for 7:45 am, I need it to ring when the clock says 7:45 am, wherever in the world I happen to be.
This is how most human beings think about time - school starts at 8 am, work starts at 9 am, McDonald's serves breakfast until 10 am, my doctor's appointment is at 4pm... these are all things that remain constant regardless of whether daylight savings time starts or ends, whether our business changes locations to a different state, whether we are conquered by the Romans and our calendar changes... so they should be stored as such, without a timezone attached.
Then from there you can combine a "date" and a "time" and a "location / timezone" to form a "datetime", to figure out the actual technological instant something needs to happen, based on the user's location and/or the location the event is going to take place.
Sometimes you really just want to express that something is going to happen at a certain time, regardless of timezone. If I set my alarm for 7:45 am, I need it to ring when the clock says 7:45 am, wherever in the world I happen to be.
This is how most human beings think about time - school starts at 8 am, work starts at 9 am, McDonald's serves breakfast until 10 am, my doctor's appointment is at 4pm... these are all things that remain constant regardless of whether daylight savings time starts or ends, whether our business changes locations to a different state, whether we are conquered by the Romans and our calendar changes... so they should be stored as such, without a timezone attached.
Then from there you can combine a "date" and a "time" and a "location / timezone" to form a "datetime", to figure out the actual technological instant something needs to happen, based on the user's location and/or the location the event is going to take place.
> Sometimes you really just want to express that something is going to happen at a certain time, regardless of timezone. If I set my alarm for 7:45 am, I need it to ring when the clock says 7:45 am, wherever in the world I happen to be.
One of my toughest engineering projects was a system that supports “every Thursday at 3pm” and the participants can be in different timezones with different DST rules. I learned more about time math and how humans think about this stuff than I ever cared to know.
One of my toughest engineering projects was a system that supports “every Thursday at 3pm” and the participants can be in different timezones with different DST rules. I learned more about time math and how humans think about this stuff than I ever cared to know.
Please share!
It took Java two tries to get their chronology standard libraries right, but eventually they did: https://docs.oracle.com/javase/8/docs/api/java/time/LocalTim...
I wrote a library ClockTime at my current job to do this. It's a thin wrapper around a "hh:mm" string for a 24 hour time and a time zone name, with some helpers.
We ran into problems like "someone in Chicago is meeting with someone in Arizona at 10am every day." Worked great until daylight savings time, which Arizona does not observe.
I've learned way more about how to think about, represent, communicate, store, and translate times than I ever thought was possible, but there are tons of war stories of people who have climbed higher on these Mountains of Madness than I can see through the fog of war.
We ran into problems like "someone in Chicago is meeting with someone in Arizona at 10am every day." Worked great until daylight savings time, which Arizona does not observe.
I've learned way more about how to think about, represent, communicate, store, and translate times than I ever thought was possible, but there are tons of war stories of people who have climbed higher on these Mountains of Madness than I can see through the fog of war.
Would you be so kind to share some insights?
I've climbed this mountain of madness trying to calculate working hours elapsed before. It was easily the project I messed up on more than any other in my career. I'm glad I did it, I'm glad I learned a lot, and I hope to never have to work on something like that again. I'd rather spend the rest of my career untangling double & triple encoded character set problems.
The biggest problem is that if even programmers - who know this is hard, and has a lot of corner cases - mess it up, expect your requirements to be very very wrong with regards to special cases.
Come up with a list of example cases and ask what the expected output should be; but not just the corner cases, the normal examples too. There's a good chance even basic requirements regarding timezone conversions have not been thought about, or are wrong. Like the earlier post said, humans really only think in local time and for short periods in the future or past. Any conversion whatsoever can end up being pretty unintuitive (even if it's right).
If it has to do with scheduling, BE EXPLICIT. Show both (or more) local times and dates and let the user pick a time for either location. Maps with pins can be a very helpful UI affordance
The biggest problem is that if even programmers - who know this is hard, and has a lot of corner cases - mess it up, expect your requirements to be very very wrong with regards to special cases.
Come up with a list of example cases and ask what the expected output should be; but not just the corner cases, the normal examples too. There's a good chance even basic requirements regarding timezone conversions have not been thought about, or are wrong. Like the earlier post said, humans really only think in local time and for short periods in the future or past. Any conversion whatsoever can end up being pretty unintuitive (even if it's right).
If it has to do with scheduling, BE EXPLICIT. Show both (or more) local times and dates and let the user pick a time for either location. Maps with pins can be a very helpful UI affordance
Echoing roadrunner's sibling comment, there's a temptation to translate your time concept into whatever time classes and libraries you are using. This works well for timestamps (a specific date and time, with or without time zone), but trying to make "10:00 am Central time" (no date) fit that just doesn't work. Peoples' interaction with calendars and time is done by sentient beings, so it's much more flexible and sloppier. But you don't have a choice - you have to meet users/business/customers where they are. It's much better to write even a thin wrapper that gives you the interface you need, than try to shoehorn the real world need into an incompatible type.
I tend to judge standard libraries by if they have different types for dates, times, absolute date/times, local date/times, zoned date/times, and time zones. I may have been bitten by time zone bugs a little too often.
Temporal has "PlainTime"
https://tc39.es/proposal-temporal/docs/plaintime.html
> A Temporal.PlainTime represents a wall-clock time, with a precision in nanoseconds, and without any time zone.
> Temporal.PlainTime refers to a time with no associated calendar date
https://tc39.es/proposal-temporal/docs/plaintime.html
> A Temporal.PlainTime represents a wall-clock time, with a precision in nanoseconds, and without any time zone.
> Temporal.PlainTime refers to a time with no associated calendar date
> If I set my alarm for 7:45 am, I need it to ring when the clock says 7:45 am, wherever in the world I happen to be.
This property can be surprisingly hard to implement on Unix epoch based systems (just ask Apple), considering time zones, DST and everything!
I wonder if this is the reason that early RTC modules would actually count time in 24 hour or even calendar-based formats, as opposed to a simple binary counter? Examples include some Game Boy cartridges [1], and I believe also Palm OS handhelds (I think they triggered an alarm once per day to recalculate the date, although I can't find a source right now).
[1] https://gbdev.io/pandocs/MBC3.html#the-clock-counter-registe...
This property can be surprisingly hard to implement on Unix epoch based systems (just ask Apple), considering time zones, DST and everything!
I wonder if this is the reason that early RTC modules would actually count time in 24 hour or even calendar-based formats, as opposed to a simple binary counter? Examples include some Game Boy cartridges [1], and I believe also Palm OS handhelds (I think they triggered an alarm once per day to recalculate the date, although I can't find a source right now).
[1] https://gbdev.io/pandocs/MBC3.html#the-clock-counter-registe...
Sometimes the realtime clocks were based on watch movements, ok, I only know of one instance, but it is fascinating. these movements were purpose built as a low power embedded device that does one thing well, drive a seven segment display to show the time. so they had to jump through some wild hoops to get it to play with the rest of the system.
https://www.youtube.com/watch?v=pr6HTiWrMmk (CuriousMarc)
https://www.youtube.com/watch?v=pr6HTiWrMmk (CuriousMarc)
So, "time with local time zone"? Tools are normally missing "time with time zone" too.
Personally, I work with a few that are missing "date" too, so at least it's symmetric. But I have no idea why no tooling has support for time data. It's such a common need, it doesn't make sense.
Personally, I work with a few that are missing "date" too, so at least it's symmetric. But I have no idea why no tooling has support for time data. It's such a common need, it doesn't make sense.
[deleted]
> If I set my alarm for 7:45 am, I need it to ring when the clock says 7:45 am, wherever in the world I happen to be.
Oh I desire more from my alarms.
Ideally I would be able to set an alarm for 7:45am and mark it "all timezones by then-local clock" ... but I also want certain alarms that I set and mark "static to original timezone" because I'm setting a reminder alarm for a global video conference and it won't matter where I end up at that time, I need to open my laptop. ... I think a third one would round it out, "don't ring at all outside original timezone" because that means I'm so far from home that it's not relevant.
Oh I desire more from my alarms.
Ideally I would be able to set an alarm for 7:45am and mark it "all timezones by then-local clock" ... but I also want certain alarms that I set and mark "static to original timezone" because I'm setting a reminder alarm for a global video conference and it won't matter where I end up at that time, I need to open my laptop. ... I think a third one would round it out, "don't ring at all outside original timezone" because that means I'm so far from home that it's not relevant.
Even what you've just described has problems because humans don't think about time in a consistent or logical way that follows real rules that work across different humans in different places. We literally do not think about time in a way that follows rigor or rules, so there cannot be a rigorous computer system which satisfies all the humans involved as it's axiomatically impossible due to being in a world with no axioms! Trying to talk about "time as humans experience it" is like trying to create a definitive list of the "best books of the century"; you can't do it because that's just, like, your opinion man.
If I'm in Portales New Mexico and I drive to see my sibling in Muleshoe Texas at what my clock says is "8:30 AM", that'd take me approximately 40 minutes, allowing me to arrive 9:10AM where I can grab a breakfast sandwich at McDonald's before meeting my sibling for shuffleboard. Except whoops I crossed a timezone discontinuity there, so actually I will arrive at 10:10AM and McDonalds will have decided to stop serving breakfast even though I'm hungry for eggs and the clock in my car happens to read "9:10AM". This isn't some reason to "do away with timezones" because that'd still have the same problem w/r/t travel and different perspectives; if I jet to London from NYC then McDs will also not be serving breakfest when I am hungry for eggs and my (unadjusted since boarding the flight) ticking Timex on my watch reads 9:10am. It's not solvable because people are experiencing different "times" in different places. Either we abolish the concept of time and it's always "whatever number my spiritual essence dictates in this moment via gazing at feeble mechanical devices that I enjoy the aesthetic of" or we swing the other way and all humans record all time in UT1 and we treat the shifting light beyond our windows as a mere inconvenience. We've done the latter with computers because they demand such rigor, implying we'll never do the former and abolish time (it's too useful!), and humans won't all abandon being bound to the sun and its vibes, so here we are in a world where we try to please the fickle and unreliable humans, ourselves, with the cold machinery of our computers (may their flickering lights forever bless us, amen).
Thus, there is only "what's an easy way to represent time that humans won't hate too much?" For most everything, that's "store it in UTC/UT1 and then convert to nearest approximation of what the user wants based factors like 'where are they now?'". Every other approach has pretty big downsides or potential failure modes. For example, if we use the system you just mentioned with a "just time" is "to do anything with it you also have to store enough info to turn it into the other representations". Thus, questions like "how far away is new years eve for my friend across the world?" will be confidently incorrect unless you account for the sun being up here and the sun being down there (typically via the timezone concept). Storing in UT1 at least means you can give the user a time that obeys axioms even if the user has to do a little legwork to adapt their brain to it, which most folks who _depend_ on timekeeping are willing to do to make sure things don't go awry.
If I'm in Portales New Mexico and I drive to see my sibling in Muleshoe Texas at what my clock says is "8:30 AM", that'd take me approximately 40 minutes, allowing me to arrive 9:10AM where I can grab a breakfast sandwich at McDonald's before meeting my sibling for shuffleboard. Except whoops I crossed a timezone discontinuity there, so actually I will arrive at 10:10AM and McDonalds will have decided to stop serving breakfast even though I'm hungry for eggs and the clock in my car happens to read "9:10AM". This isn't some reason to "do away with timezones" because that'd still have the same problem w/r/t travel and different perspectives; if I jet to London from NYC then McDs will also not be serving breakfest when I am hungry for eggs and my (unadjusted since boarding the flight) ticking Timex on my watch reads 9:10am. It's not solvable because people are experiencing different "times" in different places. Either we abolish the concept of time and it's always "whatever number my spiritual essence dictates in this moment via gazing at feeble mechanical devices that I enjoy the aesthetic of" or we swing the other way and all humans record all time in UT1 and we treat the shifting light beyond our windows as a mere inconvenience. We've done the latter with computers because they demand such rigor, implying we'll never do the former and abolish time (it's too useful!), and humans won't all abandon being bound to the sun and its vibes, so here we are in a world where we try to please the fickle and unreliable humans, ourselves, with the cold machinery of our computers (may their flickering lights forever bless us, amen).
Thus, there is only "what's an easy way to represent time that humans won't hate too much?" For most everything, that's "store it in UTC/UT1 and then convert to nearest approximation of what the user wants based factors like 'where are they now?'". Every other approach has pretty big downsides or potential failure modes. For example, if we use the system you just mentioned with a "just time" is "to do anything with it you also have to store enough info to turn it into the other representations". Thus, questions like "how far away is new years eve for my friend across the world?" will be confidently incorrect unless you account for the sun being up here and the sun being down there (typically via the timezone concept). Storing in UT1 at least means you can give the user a time that obeys axioms even if the user has to do a little legwork to adapt their brain to it, which most folks who _depend_ on timekeeping are willing to do to make sure things don't go awry.
I don't see how the problems you mentioned couldn't be solved with storing clock time together with a location (or reading that time together with determining / asking for a location), as was suggested here. Could you share your perspective on that solution?
Because users don't think about "time at a location" and they may not even have a location when they schedule a time. Both parties may say " yeah, meet at 4:30" with the knowledge that it could be my house in DST or your house in MST but we haven't decided yet, we'll figure that out in the future, but it'll be at 4:30.
That's a time outside of a location which works for the humans involved but which is completely unresolvable by the "time at a location" computer paradigm.
People do stuff like this all the time and don't think about it and it's unresolvable and not consistent.
That's a time outside of a location which works for the humans involved but which is completely unresolvable by the "time at a location" computer paradigm.
People do stuff like this all the time and don't think about it and it's unresolvable and not consistent.
Off the top of my head; the user books a meeting at 2:30am, but local gov decides to implement a timezone change - time moves forward an hour at 2am. Does your meeting still exist?
The software catches the problem during a search triggered by tzdata change, alerts the user and asks for clarification. Before user action, the meeting is considered to be scheduled to 3 AM. Is that scenario unreasonable?
That sounds right to me - for the 2-3am "time no longer exists" edge case you can apply a default pattern and ask the user to verify.
One point from this article that I think is great is: the TZ database maps pretty poorly onto how people think about timezones.
In the states, I think most people I know are familiar with "eastern time" or "pacific time." But I doubt that many of my friends recognize "Americas/New_York" or appreciate why they need to select that instead of "Americas/Indianapolis" when referencing eastern time[1]. I certainly wasn't familiar with referencing timezones this way until I had a job that cared a whole lot about timezones and had offices in several countries!
I like the idea of solving this by just asking for the location of an event.
[1] having lived in indiana a while ago, I'm assuming that Indianapolis gets its own TZ entry because it wasn't on daylight savings back in the 90s (?). Which was pretty annoying.
In the states, I think most people I know are familiar with "eastern time" or "pacific time." But I doubt that many of my friends recognize "Americas/New_York" or appreciate why they need to select that instead of "Americas/Indianapolis" when referencing eastern time[1]. I certainly wasn't familiar with referencing timezones this way until I had a job that cared a whole lot about timezones and had offices in several countries!
I like the idea of solving this by just asking for the location of an event.
[1] having lived in indiana a while ago, I'm assuming that Indianapolis gets its own TZ entry because it wasn't on daylight savings back in the 90s (?). Which was pretty annoying.
> I like the idea of solving this by just asking for the location of an event.
One issue with this phrasing is that a user might react--quite reasonably--with frustration like: "Gawd, stop asking me you stupid computer, it doesn't matter, I already told you it's an online-only teleconference, it's not really anywhere!"
I'm not sure about the best way to phrase it, but ideally we want the user to be answering: "Which clocks determine when the meeting starts?"
One issue with this phrasing is that a user might react--quite reasonably--with frustration like: "Gawd, stop asking me you stupid computer, it doesn't matter, I already told you it's an online-only teleconference, it's not really anywhere!"
I'm not sure about the best way to phrase it, but ideally we want the user to be answering: "Which clocks determine when the meeting starts?"
The colloquial names people use, in particular "central time", aren't globally unique. I.e., if someone says "central time", to map that to "America/Chicago" usually involves a set of assumptions about being in America in the first place.
So, yeah, you either use location, present major nearby cities, etc. to get around the fact that the ordinary person has no idea what the identifier of their timezone is.
So, yeah, you either use location, present major nearby cities, etc. to get around the fact that the ordinary person has no idea what the identifier of their timezone is.
Yeah, I think that gets at the fact that it's typically "obvious" what you mean in context - which is part of why I think the TZ database names can feel so off. Why in the world do you need that much precision?!
(We of course, both know why! And if you haven't seen the names before but you're a programmer, youprobably still make some assumptions about edge cases, precision, etc. But I'm assuming that is not the experience of the typical Google Calendar user?)
(We of course, both know why! And if you haven't seen the names before but you're a programmer, youprobably still make some assumptions about edge cases, precision, etc. But I'm assuming that is not the experience of the typical Google Calendar user?)
There's an interesting twist to this problem when one is playing with DST! Consider for instance a recurring event that happens every day at 02:30, let's say in Switzerland.
On the day DST starts, that event cannot exist, at 01:59 clocks will jump forward to 03:00. Vice versa, on the day DST ends, any time between 02:00 and 02:59 will happen twice.
On the day DST starts, that event cannot exist, at 01:59 clocks will jump forward to 03:00. Vice versa, on the day DST ends, any time between 02:00 and 02:59 will happen twice.
That's why DST is an abomination which must be abolished. Just say work/school starts at 7am instead of 8am during a part of the year. Simple.
The the school, and every employer, and every government agency, and the stores, and the public transportation, and so on and so forth.... will have to all agree to change their hours. On the same day!
You might not like DST, but leaving it in the hands of every entity to interpret is a far worse situation.
You might not like DST, but leaving it in the hands of every entity to interpret is a far worse situation.
> will have to all agree to change their hours. On the same day!
Imagine if there was a powerful body representing the population that could decree a change like this!
Instead, we only have a body powerful enough to decree that all of those people change their clocks on the same day.
Anyway, the best part of changing the hours instead of the clock is that not everybody needs to do it on the same day.
Imagine if there was a powerful body representing the population that could decree a change like this!
Instead, we only have a body powerful enough to decree that all of those people change their clocks on the same day.
Anyway, the best part of changing the hours instead of the clock is that not everybody needs to do it on the same day.
> That's why DST is an abomination which must be abolished.
Don't scapegoat it too much: The underlying problem of time-shifts will still exist, and that edge-case still needs to be handled by other means.
DST just makes shifts more frequent and obvious. One might even say that it draws an important amount of attention to a broader class of problems.
Don't scapegoat it too much: The underlying problem of time-shifts will still exist, and that edge-case still needs to be handled by other means.
DST just makes shifts more frequent and obvious. One might even say that it draws an important amount of attention to a broader class of problems.
That's a much less simple solution at the societal level, which is why it probably won't ever happen.
How many real humans would have a recurring event at 02:30 on Sunday?
Some people work night shifts
And the people that do work night shifts, have their shifts either shortened or extended by an hour as well (at least in Germany, from what I’m aware)
Before companies went global 24/7, this was really a rare problem.
These days, you could have a global company where some emergency occurs and you have to call a meeting at sometime on Saturday wrt your location timezone, but one of the other locations is several hours ahead of your timezone such that the meeting occurs during/around the transition period for their location's timezone.
So, not common at all. But it may happen. That's why they choose the transition times to occur when most of the LOCAL population DOES NOT schedule meetings.
I don't think there were any Saturday/Sunday virtual meetings when this timezone stuff was created... Simpler times...
These days, you could have a global company where some emergency occurs and you have to call a meeting at sometime on Saturday wrt your location timezone, but one of the other locations is several hours ahead of your timezone such that the meeting occurs during/around the transition period for their location's timezone.
So, not common at all. But it may happen. That's why they choose the transition times to occur when most of the LOCAL population DOES NOT schedule meetings.
I don't think there were any Saturday/Sunday virtual meetings when this timezone stuff was created... Simpler times...
A common pitfall I see developers stumble on is underestimating how often timezones, DST rules, etc change. In most of our daily lives this is something that happens very rarely, right? However, if you go look at the change logs for DST rules, timezones, etc, you'll see that as a ballpark figure there's at least one change a year, and on many years more. Once a year is way too often to not account for this data changing and the recommendations given in this article are solid.
> There’s a variant of this advice which you’re more likely to hear from the PostgreSQL faithful: use TIMESTAMP WITH TIME ZONE or its convenient alias timestamptz. This stores the exact value in UTC and sounds like it might store the timezone too... but it doesn’t! All that’s stored is that UTC value, converted from whatever timezone was active or specified when the value was inserted.
This behavior was changed in a more recent edition of the SQL standard. Now, we have `TIMESTAMP WITH LOCAL TIME ZONE` which works the way `TIMESTAMP WITH TIME ZONE` used to work: basically just converting everything to/form UTC when it's stored/retrieved. The semantics for `TIMESTAMP WITH TIME ZONE` are now supposed to work more like the author's suggested approach: just gluing a time zone name to a regular old timestamp.
I'm not sure when Postgres will adopt the newer type semantics. Oracle has for a while now: https://docs.oracle.com/en/database/oracle/oracle-database/1...
This behavior was changed in a more recent edition of the SQL standard. Now, we have `TIMESTAMP WITH LOCAL TIME ZONE` which works the way `TIMESTAMP WITH TIME ZONE` used to work: basically just converting everything to/form UTC when it's stored/retrieved. The semantics for `TIMESTAMP WITH TIME ZONE` are now supposed to work more like the author's suggested approach: just gluing a time zone name to a regular old timestamp.
I'm not sure when Postgres will adopt the newer type semantics. Oracle has for a while now: https://docs.oracle.com/en/database/oracle/oracle-database/1...
What are you talking about? Timestamp with local time zone is exactly the semantics that is broken and can corrupt your data by just having a server set-up wrong.
Postgres has had that exact semantics for ages, and the documentation explicitly tells you in very large text that it only exists for compatibility reasons and that you shouldn't use it.
The correct semantics for "timestamp with local time zone" is the naive one, that you can only recover using strings nowadays, thanks to the SQL standard braindead decision. But the good (?) news is that if you have a problem that requires a database, it's very likely that you will need to handle time zones anyway, so it's not a big issue.
Postgres has had that exact semantics for ages, and the documentation explicitly tells you in very large text that it only exists for compatibility reasons and that you shouldn't use it.
The correct semantics for "timestamp with local time zone" is the naive one, that you can only recover using strings nowadays, thanks to the SQL standard braindead decision. But the good (?) news is that if you have a problem that requires a database, it's very likely that you will need to handle time zones anyway, so it's not a big issue.
I can't actually tell what you're saying. Postgres doesn't have a timestampltz. Only timestamptz. Oracle has both, but their timestampltz behaves like Postgres' timestamptz, and their timestamptz behaves the way the author is suggesting people should handle timestamps. Oracle is using the newer ISO semantics. I wish it were easier to get a copy of the ISO standard I used to access at work.
I once was hours late for a doctor appointment, because I entered it into my calendar app while on vacation. That taught this particular lesson pretty effectively.
The choice to mash absolute time and clock time into the same formats and APIs is one of the greatest mistakes in the history of programming, somewhere under null pointers.
I recently had a similar experience on vacation. I entered all of the shows and events I was attending in Vegas into my iOS calendar while home in NY. Upon arriving all my events were 3 hours off, and I had to manually correct each one.
A cursory look through Calendar didn’t turn up any way to enter time zone independent events.
A cursory look through Calendar didn’t turn up any way to enter time zone independent events.
Same thing happened to me on Android.
But Google Calendar also had the time for the departure and reurn flights blocked out (including departure airport/arrival airport both ways), yet it was too dumb to notice I was setting an appt for 8PM for a Las Vegas location - but Google Calendar decided to put an Eastern USA timezone on the appt.
That's how you know the Google Calendar devs/PMs don't travel often. And also none of the same Google devs/PMS cared enough nor high-level Google execs themselves use Google Calendar - cuz if they had the same thing happen to them, you'd know that it would have been fixed before the next performance review.
But Google Calendar also had the time for the departure and reurn flights blocked out (including departure airport/arrival airport both ways), yet it was too dumb to notice I was setting an appt for 8PM for a Las Vegas location - but Google Calendar decided to put an Eastern USA timezone on the appt.
That's how you know the Google Calendar devs/PMs don't travel often. And also none of the same Google devs/PMS cared enough nor high-level Google execs themselves use Google Calendar - cuz if they had the same thing happen to them, you'd know that it would have been fixed before the next performance review.
It is important to note that this is only relevant for future events where legal/civil time, a means for squishy humans to coordinate actions in the future, is what matters.
For instances like “bake my bread for one hour” you care about elapsed time/duration which is not subject to the whims of civil time as you are coordinating with the laws of physics, or gods of cooking fickle as they may be, so a absolute second offset such as TAI should be used.
And for the past, the unambiguous correct answer is TAI or other absolute second offset. Period.
This is because the exact time of occurrence of a past event is, ignoring relativity, fixed and corresponds to a specific second/time. This is completely unambiguous and can be converted, entirely losslessly and roundtripped, to whatever the corresponding civil time would have been in any timekeeping system you so desire. But again, this applies to the past only. The mapping between seconds to civil time and vice versa is subject to the whims of your government until then.
For instances like “bake my bread for one hour” you care about elapsed time/duration which is not subject to the whims of civil time as you are coordinating with the laws of physics, or gods of cooking fickle as they may be, so a absolute second offset such as TAI should be used.
And for the past, the unambiguous correct answer is TAI or other absolute second offset. Period.
This is because the exact time of occurrence of a past event is, ignoring relativity, fixed and corresponds to a specific second/time. This is completely unambiguous and can be converted, entirely losslessly and roundtripped, to whatever the corresponding civil time would have been in any timekeeping system you so desire. But again, this applies to the past only. The mapping between seconds to civil time and vice versa is subject to the whims of your government until then.
The relationship between absolute points in time and calendar/clock/tz components can be legally changed retroactively.
Just like with any other kind of data, store whatever you are semantically representing. If you are representing an absolute point in time (bread timer), store a Unix timestamp. If you are representing time components, AKA relative time, AKA human time, AKA calendar/clock/tz time, then store those components.
Also, most past clock times we care to store start out as future times.
Just like with any other kind of data, store whatever you are semantically representing. If you are representing an absolute point in time (bread timer), store a Unix timestamp. If you are representing time components, AKA relative time, AKA human time, AKA calendar/clock/tz time, then store those components.
Also, most past clock times we care to store start out as future times.
The relationship between the occurrence of a past event and the second it occurred can not be changed, barring relativity. As such, storing the absolute second offset unambiguously persists the actual time the event occurred regardless of any legal changes.
It will just convert to a different, now agreed upon, civil time according to the calendar chosen. It is no different from citing the current second according to the Gregorian calendar versus the Mayan calendar. They both refer to the same “time”, but use different civil dates. The underlying thing that is convertible to different calendar formats is the real thing and corresponds to the absolute time of the past event.
edit: And no, it is almost guaranteed that most past time events are timestamps of the “current” time which has the same qualities as “past” time. Only future civil times should not use absolute second offset times.
It will just convert to a different, now agreed upon, civil time according to the calendar chosen. It is no different from citing the current second according to the Gregorian calendar versus the Mayan calendar. They both refer to the same “time”, but use different civil dates. The underlying thing that is convertible to different calendar formats is the real thing and corresponds to the absolute time of the past event.
edit: And no, it is almost guaranteed that most past time events are timestamps of the “current” time which has the same qualities as “past” time. Only future civil times should not use absolute second offset times.
The relationship can't be changed retroactively, or at least not in a practical de facto way. For example "what UTC was at the moment when clocks in Elbonia showed 5PM on December 1st 2024" is a fact that has already happened and won't un-happen just because the Elbonian government passes a law claiming otherwise.
What you do need to worry about is the relationship being after an event has been planned and before the event occurs.
What you do need to worry about is the relationship being after an event has been planned and before the event occurs.
I think this is a really good approach to handling events that someone might invite someone else to in real life. No one really ever talks about time zone or daylight savings time when you're texting your friend about a party that's happening. It's always obvious from context.
I've been working on an end-to-end encrypted event invitation service and I chose to ignore timezones completely and just have a date picker and free-form text field for time. If users want to export the event as an ical file I just set it as an all-day event and put the time in the body of the event. It's not perfect, but it's usable enough for most people.
The one exception here is importing to Google Calendar which for some awful reason imports all day events with no time zone as 24 hour long events from 12am to 12am UTC the following day.
(I have a silly beta version of the app up at https://pinvite.app if anyone cares to try it out)
I've been working on an end-to-end encrypted event invitation service and I chose to ignore timezones completely and just have a date picker and free-form text field for time. If users want to export the event as an ical file I just set it as an all-day event and put the time in the body of the event. It's not perfect, but it's usable enough for most people.
The one exception here is importing to Google Calendar which for some awful reason imports all day events with no time zone as 24 hour long events from 12am to 12am UTC the following day.
(I have a silly beta version of the app up at https://pinvite.app if anyone cares to try it out)
Another good article that discusses the same topic in detail: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a...
And previous threads on HN for that URL (oldest first):
https://news.ycombinator.com/item?id=19500640
https://news.ycombinator.com/item?id=30659164
https://news.ycombinator.com/item?id=41043499
https://news.ycombinator.com/item?id=19500640
https://news.ycombinator.com/item?id=30659164
https://news.ycombinator.com/item?id=41043499
I’ve thought about the proposed solution before (spoiler: the author proposes to store the user’s intended date/time AND the TZ qualified date/time.)
The problem is that, except for UTC, any timestamp relating to a user’s intention must ALSO store a geolocation. Dinner at 6pm? Great! Where (on earth)?
And I’m not sure what to make of storing a TZ qualified timestamp AND a non-TZ qualified timestamp. I’m not sure what problem that solves or how to reconcile if they differ.
I’m still in the “store as UTC” and be very careful about disambiguating at creation time.
The problem is that, except for UTC, any timestamp relating to a user’s intention must ALSO store a geolocation. Dinner at 6pm? Great! Where (on earth)?
And I’m not sure what to make of storing a TZ qualified timestamp AND a non-TZ qualified timestamp. I’m not sure what problem that solves or how to reconcile if they differ.
I’m still in the “store as UTC” and be very careful about disambiguating at creation time.
My recommendation is actually to store the local time and the exact location, if you can get it. Then use that location to determine the correct timezone.
Some users may not know that their Timezone is America/Los_Angeles but they should hopefully know where their venue is.
You always treat the local time - 6pm on 5th December in Boise, Idaho - as the core truth and derive the UTC version from that. Then if Idaho decide to cancel daylight savings time you can update your calculated and stored UTC timestamps to the correct values.
Some users may not know that their Timezone is America/Los_Angeles but they should hopefully know where their venue is.
You always treat the local time - 6pm on 5th December in Boise, Idaho - as the core truth and derive the UTC version from that. Then if Idaho decide to cancel daylight savings time you can update your calculated and stored UTC timestamps to the correct values.
Hmm, some hard parts for automation left unsaid.
> use that location to determine the correct timezone
Does this mean consult an "address to timezone lookup service?" Not sure if they exist, and I don't see another way without user specifying manually. Maybe a string match, but it could be wrong/unsure somewhat often.
> Then if Idaho decide to cancel daylight savings time you can update
Looking up events within a time interval is easy enough, those in city (timezone?) limits not so much. Consult a GIS system, search within polygons??? String match or every city in your database?
> use that location to determine the correct timezone
Does this mean consult an "address to timezone lookup service?" Not sure if they exist, and I don't see another way without user specifying manually. Maybe a string match, but it could be wrong/unsure somewhat often.
> Then if Idaho decide to cancel daylight savings time you can update
Looking up events within a time interval is easy enough, those in city (timezone?) limits not so much. Consult a GIS system, search within polygons??? String match or every city in your database?
They do exist: many geocoding APIs (address to lat/lon) can optionally return a timezone as well, like this one: https://opencagedata.com/api#forward-resp
Or you can get a latitude/longitude and derive the timezone from that - I built an API for that here https://timezones.datasette.io/timezones/by_point?longitude=...
Or you can get a latitude/longitude and derive the timezone from that - I built an API for that here https://timezones.datasette.io/timezones/by_point?longitude=...
Danke. I wonder about making one's business dependent on such services however.
It's possible these days to run an entirely open source geocoding stack of your own. Here's one of the better options for that: https://github.com/osm-search/Nominatim
I wouldn't recommend it though: it needs a beefy machine and applying updates on an ongoing basis is fiddly. Better to use a commercial option and have that as an escape hatch.
I wouldn't recommend it though: it needs a beefy machine and applying updates on an ongoing basis is fiddly. Better to use a commercial option and have that as an escape hatch.
My recommendation is actually to store the local time and the exact location, if you can get it. Then use that location to determine the correct timezone.
Some users may not know that their Timezone is America/Los_Angeles but they should hopefully know where their venue is.
Some users may not know that their Timezone is America/Los_Angeles but they should hopefully know where their venue is.
The location is implied by the time zone. The proposal is to store the user’s calendar date, wall-clock time, and time zone. (Technically, you also need a DST marker for the ambiguous hour in the fall when the clock is turned back and repeats 2–3 am.) From that you can derive a UTC timestamp.
So I’ve been wondering this for a while. Why don’t we actually use a „spacetime“ format, like 2024-12-05T23:03:47+48.86,2.34 instead of bothering with time zones at all? Why not use arbitrary precision coordinates in their place? That would solve a bunch of issues with time zones at once, like not needing to know which time offset existed at the point in time in question. Depending on the use case, we could make the coordinates more precise, but regularly, I’d wager one or two digits would suffice for current timestamp usage.
There's usually a need for a human to see/understand the timezone information - probably doesn't matter how you store it, but showing JUST the RAW spacetime format will annoy a certain group of users (the 99% who don't/can't map from lat/long or whatever format you choose to a more human-legible "hometown, country" format)
But then you'd still need to figure out in what timezone those coordinates are to know when it is/was, so you made the problem even more complex. Unless you're suggesting we get rid of timezones in general, in which case it would be the same time everywhere and we don't need anything for disambiguation, i.e. we can get rid of the coordinates.
Inb4 that other blog post that explains why getting rid of timezones is troublesome in everyday life.
Inb4 that other blog post that explains why getting rid of timezones is troublesome in everyday life.
It's more complex, but usually it better matches user intent. If I say the conference starts on 2027-04-05 13:09 in Berlin, and Germany decides to change their time zones or daylight saving time rules sometime in 2025, your stored offset or stored UTC time is now wrong. The conference will start whenever local clocks show 13:09, and you have no reliable way of knowing which time zone local clocks will be in in two years time.
Of course if we go down that path we should also add a format to indicate offsets. If I say I'll be back in 10 minutes, that interval shouldn't change no matter what happens to the time zone. To perfectly disambiguate it you would need to store it as current time (with timezone or UTC) plus the intended offset. This would also "solve" a lot of issues around leap seconds and the inability to predict them more than six months into the future.
Of course if we go down that path we should also add a format to indicate offsets. If I say I'll be back in 10 minutes, that interval shouldn't change no matter what happens to the time zone. To perfectly disambiguate it you would need to store it as current time (with timezone or UTC) plus the intended offset. This would also "solve" a lot of issues around leap seconds and the inability to predict them more than six months into the future.
The geo lookup would be expensive computationally, and also there are arguments over geographic boundaries that could make TZ lookup ambiguous (the author gave an example in the article)
> The geo lookup would be expensive computationally
Not really? There's not that many timezones, globally, and I think their polygon data is in OSM / could be obtained from OSM. Simply brute-force colliding a single point with all of them I would expect to be basically "instantaneous", but there are a number of trivial optimizations, like bounding boxes, that you could do to speed it up.
(Point-in-polygon is not terribly expensive; it's O(segments) in your polygon.)
That said, there are problems with that, as you mention. I just don't think "computationally expensive" is one of them.
Not really? There's not that many timezones, globally, and I think their polygon data is in OSM / could be obtained from OSM. Simply brute-force colliding a single point with all of them I would expect to be basically "instantaneous", but there are a number of trivial optimizations, like bounding boxes, that you could do to speed it up.
(Point-in-polygon is not terribly expensive; it's O(segments) in your polygon.)
That said, there are problems with that, as you mention. I just don't think "computationally expensive" is one of them.
Oddly enough I built an API a few years ago that does exactly that, using timezone polygons derived from OpenStreetMap: https://timezones.datasette.io/timezones/by_point?longitude=...
I was hoping this article would cover non-obvious date time quirks when conveying this to humans.
If the context is an event, what does midnight mean? A one-day event might start at 6pm but not end until 2 or 3am. A computer considers that a two-day event, but that's kind of intuitively wrong. To a human, it's only a 1 day, and it doesn't end tomorrow. It ends, late tonight.
What if it starts Saturday at 9pm and goes until 6am? You would still consider that a Saturday event, but ends at sunrise, or early morning.
If the context is an event, what does midnight mean? A one-day event might start at 6pm but not end until 2 or 3am. A computer considers that a two-day event, but that's kind of intuitively wrong. To a human, it's only a 1 day, and it doesn't end tomorrow. It ends, late tonight.
What if it starts Saturday at 9pm and goes until 6am? You would still consider that a Saturday event, but ends at sunrise, or early morning.
> And if some legislature somewhere on earth makes a surprising change to their DST rules, we can identify all of the events that are affected by that change and update that denormalized UTC time accordingly.
And how do we automate getting to know about, and performing, those updates in our application databases? If that is not easily automatable, I think storing the UTC time as well will cause problems.
Maybe the UTC time should be a calculated column?
And how do we automate getting to know about, and performing, those updates in our application databases? If that is not easily automatable, I think storing the UTC time as well will cause problems.
Maybe the UTC time should be a calculated column?
I suggest storing a "denormalized UTC time", a calculated column would work fine for that.
For automation: install the latest tz database (I use pytz for this, usually) and then run a batch job to see what's changed. Not trivial but not impossible either. Running events websites is hard!
For automation: install the latest tz database (I use pytz for this, usually) and then run a batch job to see what's changed. Not trivial but not impossible either. Running events websites is hard!
I had a little page that would encode Unix time of the event in the url, then just count down.
Super reliable.
Super reliable.
Doesn't really solve the problem mentioned, does it? I decide to generate a link which counts down to doctor's appointment 8 months from now, and in 6 months my country decides to change DST rules. The countdown is now wrong.
https://news.ycombinator.com/item?id=42259689 14 days ago
https://news.ycombinator.com/item?id=42281983 11 days ago
https://news.ycombinator.com/item?id=42358025 3 days ago
https://news.ycombinator.com/item?id=42364372 reportedly 3 days ago but it's the same HN item ID as this one (second chance pool maybe?)
It's barely 2 weeks old, is it worth reposting that often?
Semi-related, I remember submitting something I found interesting that had been posted iirc ~40 days ago, but had barely gotten any votes so I submitted it once more and i just get redirected to the old ID and the system refuses to create a new entry. Okay no problem, I'll post later^W^W forget about it, but that makes me wonder: how can 4 submissions in two weeks even physically happen?