Storing Event Times in Global Applications

For applications that serve a global community, storing the date and time of an event in only UTC can be insufficient. Storing only the server time can be even worse.

The times of important events should instead be captured as tuples consisting of:

1. The date and time when the event occurred in the UTC timezone
2. The local timezone name where the event occurred
3. The local date and time when the event occurred, with the timezone’s UTC offset, per ISO8601. This information is more defensible than storing only UTC because daylight savings tables can change on the whims of politicians, as they did in the United States in 2007. Although it’s possible to store the timezone offset as a small integer in order to save disk space, the ISO8601 string is easier to use; for example, the ISO8601 format is easy to sort. Use the “datetime with timezone offset” datatype if it is available to you.

Those who say this is overkill (of course some will) lack the domain experience to conclude otherwise. If an event is important to you — especially for legal reasons — treat it that way.

On the other hand, server times can be stored as UTC only, because data centers don’t usually change locations. If you have multiple data centers and aggregate data into a common store, you will eventually want to be able to view event times in local time for incident analysis. Plan ahead by storing data center identifiers with other event details. A simple data-center-id-to-timezone table is probably sufficient for local time conversions.

Server time is only loosely related to the time of real-world events. Event times must be captured as accurately as possible prior to sending them to durable storage. Plan for unexpected delays such as temporary network outages.

Repeating Events

While we’re on this topic, repeating events like “2:00 PM on Monday” are implicitly in the local time zone. The time should always be 2:00 PM regardless of DST. Never convert repeating date/time instructions to UTC (another rookie mistake).