Simon Willison’s Weblog

Storing times for human events

27th November 2024

I’ve worked on various event websites in the past, and one of the unintuitively difficult problems that inevitably comes up is the best way to store the time that an event is happening. Based on that past experience, here’s my current recommendation.

This is the expanded version of a comment I posted on lobste.rs a few days ago, which ended up attracting a bunch of attention on Twitter.

• The problem
• The “best practice” that isn’t
• Things that can go wrong
• User error
• International timezone shenanigans
• Microsoft Exchange and the DST update of 2007
• My recommendation: store the user’s intent time and the location/timezone
• Timezone UIs suck, generally

The problem

An event happens on a date, at a time. The precise details of that time are very important: if you tell people to show up to your event at 7pm and it turns out they should have arrived at 6pm they’ll miss an hour of the event!

Some of the worst bugs an events website can have are the ones that result in human beings traveling to a place at a time and finding that the event they came for is not happening at the time they expected.

So how do you store the time of an event?

The “best practice” that isn’t

Any time you talk to database engineers about dates and times you’re likely to get the same advice: store everything in UTC. Dates and times are complicated enough that the only unambiguous way to store them is in UTC—no daylight savings or timezones to worry about, it records the exact moment since the dawn of the universe at which the event will take place.

Then, when you display those times to users, you can convert them to that user’s current timezone—neatly available these days using the Intl.DateTimeFormat().resolvedOptions().timeZone browser API.

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.

In either case, we are losing critical information about when that event is going to happen.

Things that can go wrong

What’s wrong with calculating the exact UTC time the event is starting and storing only that?

The problem is that we are losing crucial details about the event creator’s original intent.

If I arrange an evening meetup for next year on December 3rd at 6pm, I mean 6pm local time, by whatever definition of local time is active on that particular date.

There are a number of ways this time can end up misinterpreted:

• User error: the user created the event with an incorrect timezone
• User error: the user created the event in the wrong location, and later needs to fix it
• International timezone shenanigans: the location in which the event is happening changes its timezone rules at some point between the event being created and the event taking place

User error

By far the most common issue here is user error with respect to how the event was initially created.

Maybe you asked the user to select the timezone as part of the event creation process. This is not a particularly great question: most users don’t particularly care about timezones, or may not understand and respect them to the same extent as professional software developers.

If they pick the wrong timezone we risk showing the wrong time to anyone else who views their event later on.

My bigger concern is around location. Imagine a user creates their event in Springfield, Massachusetts... and then a few days later comes back and corrects the location to Springfield, Illinois.

That means the event is happening in a different timezone. If the user fails to update the time of the event to match the new location, we’re going to end up with an incorrect time stored in our database.

International timezone shenanigans

One of my favourite niche corners of the internet is the tz@iana.org mailing list. This is where the maintainers of the incredible open source tz database hang out and keep track of global changes to timezone rules.

It’s easy to underestimate how much work this is, and how weird these rule changes can be. Here’s a recent email proposing a brand new timezone: Antarctica/Concordia:

Goodmorning. I’m writing here to propose a new time zone for an all-year open Antarctic base. The base is a French–Italian research facility that was built 3,233 m (10,607 ft) above sea level at a location called Dome C on the Antarctic Plateau, Antarctica. https://en.wikipedia.org/wiki/Concordia_Station

The timezone is UTC+8 without DST.

That’s a pretty easy one. Here’s a much more complicated example from March 2023: Lebanon DST change internally disputed:

Lebanon is going through many internal disputes surrounding the latest decision to delay DST. Many institutions are refusing to comply with the change and are going to adopt regular DST on Sunday Mar 26th. Those institutions include but are not limited to:

• News agencies
• Religious organizations
• Schools, universities, etc...

The refusal is mainly centered the legality of that decision and, obviously, the technical chaos it will create because of its short notice. Moreover, as some of the below articles mention, this is also causing sectarian strife.

Lebanon ended up with more than one timezone active at the same time, depending on which institution you were talking to!

It’s surprisingly common for countries to make decisions about DST with very little notice. Turkey and Russia and Chile and Morocco are four more examples of countries that can often cause short-term chaos for software developers in this way.

If you’ve stored your event start times using UTC this is a big problem: the new DST rules mean that an already-existing event that starts at 6pm may now start at 5pm or 7pm local time, according to the UTC time you’ve stored in your database.

Microsoft Exchange and the DST update of 2007

Via fanf on Lobsters I heard about a fascinating example of this problem in action. In 2005 the Bush administration passed the Energy Policy Act of 2005, one part of which updated the rules for when DST would start across most of the USA.

This resulted in a bug where Microsoft Exchange and Outlook would display appointment times incorrectly! From Exchange Server and Daylight Saving Time (DST) 2007:

After installing the DST updates, all old recurring and single instance appointments that occur during the delta period between the DST 2007 rules and the previous DST rules will be one hour later. These appointments will need to be updated so that they will display correctly in Outlook and Outlook Web Access, and for CDO based applications.

Microsoft released a special “Exchange Calendar Update Tool” executable for people to run to fix all of those upcoming calendar events.

My recommendation: store the user’s intent time and the location/timezone

My strong recommendation here is that the most important thing to record is the original user’s intent. If they said the event is happening at 6pm, store that! Make sure that when they go to edit their event later they see the same editable time that they entered when they first created it.

In addition to that, try to get the most accurate possible indication of the timezone in which that event is occurring.

For most events I would argue that the best version of this is the exact location of the venue itself.

Users may find timezones confusing, but they hopefully understand the importance of helping their attendees know where exactly the event is taking place.

If you have the venue location you can almost certainly derive the timezone from it. I say almost because, as with anything involving time, there are going to be edge-cases—most critically for venues that are exactly on the line that divides one timezone from another.

I haven’t sat down to design my ideal UI for this, but I can imagine something which makes it abundantly clear to the user exactly where and when the event is taking place at that crucial local scale.

Now that we’ve precisely captured the user’s intent and the event location (and through it the exact timezone) we can denormalize: figure out the UTC time of that event and store that as well.

This UTC version can be used for all sorts of purposes: sorting events by time, figuring out what’s happening now/next, displaying the event to other users with its time converted to their local timezone.

But when the user goes to edit their event, we can show them exactly what they told us originally. When the user edits the location of their event we can maintain that original time, potentially confirming with the user if they want to modify that time based on the new location.

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.

Timezone UIs suck, generally

As an aside, here’s my least favorite time-related UI on the modern internet, from Google Calendar:

There isn’t even a search option! Good luck finding America/New_York in there, assuming you knew that’s what you were looking for in the first place.