It's an interesting article I read and shared with friends when I first saw it, and recommend anyone to read who is not already knowledgeable on the topic, but eh<p><a href="https://news.ycombinator.com/item?id=42259689">https://news.ycombinator.com/item?id=42259689</a> 14 days ago<p><a href="https://news.ycombinator.com/item?id=42281983">https://news.ycombinator.com/item?id=42281983</a> 11 days ago<p><a href="https://news.ycombinator.com/item?id=42358025">https://news.ycombinator.com/item?id=42358025</a> 3 days ago<p><a href="https://news.ycombinator.com/item?id=42364372">https://news.ycombinator.com/item?id=42364372</a> reportedly 3 days ago but it's the same HN item ID as this one (second chance pool maybe?)<p>It's barely 2 weeks old, is it worth reposting that often?<p>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?
I think one very common situation you are missing in "Things that can go wrong" is repeating events:<p>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.<p>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:<p><a href="https://tc39.es/proposal-temporal/docs/strings.html" rel="nofollow">https://tc39.es/proposal-temporal/docs/strings.html</a><p><pre><code> 2007-12-03T10:15:30+01:00[Europe/Paris]
</code></pre>
> Because of this format's long-term industry adoption, it was chosen for use in ECMAScript Temporal for both input and output.<p>> 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.<p>It is very important to use a high quality time library that forces you to think about these things.
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".<p>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.<p>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.<p>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.
One point from this article that I think is great is: the TZ database maps pretty poorly onto how people think about timezones.<p>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!<p>I like the idea of solving this by just asking for the location of an event.<p>[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.
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.<p>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.
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.
<i>> 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.</i><p>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.<p>I'm not sure when Postgres will adopt the newer type semantics. Oracle has for a while now: <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html" rel="nofollow">https://docs.oracle.com/en/database/oracle/oracle-database/1...</a>
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.
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.<p>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.<p>And for the past, the unambiguous correct answer is TAI or other absolute second offset. Period.<p>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.
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.<p>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.<p>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.<p>(I have a silly beta version of the app up at <a href="https://pinvite.app" rel="nofollow">https://pinvite.app</a> if anyone cares to try it out)
Another good article that discusses the same topic in detail: <a href="https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/" rel="nofollow">https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a...</a>
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.)<p>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)?<p>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.<p>I’m still in the “store as UTC” and be very careful about disambiguating at creation time.
I was hoping this article would cover non-obvious date time quirks when conveying this to humans.<p>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.<p>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.<p>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.<p>Maybe the UTC time should be a calculated column?