Timezones, DST, UTC, and database time

posted: March 14, 2021

tl;dr: The more you program computers, the more you love UTC...

One of the pleasures of living in Arizona is not having to change local time due to Daylight Savings Time. Unfortunately, Daylight Savings Time (DST) still causes Arizonans some confusion because the rest of the country changes their local time, which moves the time difference with Arizona by an hour. Business meetings with Californians that used to take place at 11am in the winter move to 10am local time in the spring, if the Californians’ are dominant; it’s a bit of a power play.

Someday, I believe, the country and the world will give up Daylight Savings Time: there are mental and physical health reasons for doing so. In the meantime, DST and timezones create major ongoing challenges for software developers. When an event occurs in a computer system and needs to be recorded, what timestamp shall be used for that event? Local time? Local time with or without DST taken into account? Some sort of universal timestamp? Then later, when the record of that event is displayed to a user, shall the time of day be displayed exactly as it is stored? Or shall it be adjusted for the local timezone of the user, again with or without taking DST into account? These are tougher questions to answer than they may appear to be at first.

Let’s suppose you’re programming an email service, and for each email you need to record the time that the email was sent and the time that it was received. Do you use the sender’s timezone? The recipient’s? If they are in locations in different timezones, say Arizona and Tokyo, someone is not going to be happy if you choose one of those two timezones and then display the same timestamp to both users without any adjustment to local time. An email sent from Phoenix at 9:08am on Sunday, March 14, 2021 might be received in Tokyo at 1:09am on Monday, March 15, 2021. The Arizona person is not going to be happy if you tell him or her that the email was sent on March 15 at 1:08am, and the Tokyo person is not going to be happy if you tell him or her that the email was received at 9:09 am on Sunday, March 14.

What time is it? What day is it? These are hard questions to answer...

The best solution to this common problem is use UTC (Coordinated Universal Time) in the internals of the computer system itself, including the database(s), and to convert timestamps to the user’s local time as close to the user as possible, at what is known as the presentation layer in the OSI model. In the example above, the system would record that the email was sent/received at 4:08pm/4:09pm on Sunday, March 14, 2021 UTC time. In this era of cloud-based computing, distributed systems, and globalization, many companies have decided to do this, although I still run across companies who like to set up their databases using the timezone of the company headquarters location. This introduces another potential problem: what happens when the headquarters location moves across the country?

Assuming the database and the system software are using UTC for creating timestamps, the challenge then becomes learning each user’s timezone so that the proper adjustment to local time can be performed. The problem is a plethora of choices: do you use the geographic location of the device the user is using, which may be a mobile phone with GPS? But then, as the user travels cross country, should the timestamps of the emails in the user’s inbox be adjusted? Another possibility is to use the user’s IP address, which can usually be traced to a geographic location. But the user may be using a VPN that terminates in a different timezone, which would cause the local timezone adjustment to be made to an incorrect timezone. To avoid these problems, devices such as mobile phones and computers (via their operating systems), and applications, and cloud-based services will usually ask users to configure their preferred timezone. This is so that internal timestamps, typically UTC, can be converted into the user’s desired timezone when the timestamps are displayed.

The solution described so far produces a good user experience, but there is still the issue of how the system is monitored and how reports are produced from the data, for the company operating the system. Let’s say someone at the company wants a daily report containing metrics on all the emails sent that day, or even a listing of every email sent, which includes the timestamp. Well, what exactly defines a day? Is it from midnight to midnight, UTC time? For an Arizona-based company, that would be from 5pm one day to 5pm the next day. Maybe the company wants to produce a report that runs from midnight to midnight, local time. But what about Daylight Savings Time? If DST is taken into account, then every year there will be one report with 23 hours worth of data (in the spring, when clocks are set forward and an hour is skipped) and one report with 25 hours worth of data (in the fall, when they are set back and an hour is repeated). If DST is not taken into account, then every report will have 24 hours worth of data, but the day being measured will be an hour off of local time for a good portion of the year.

There’s no great solution that works for everyone for the reporting issue. As someone who often deals with the internals of a system, I prefer to use UTC time. But business-oriented folks may prefer local time, with or without an adjustment for DST. One of these days, hopefully, the rest of the world will stop using DST. Ultimately, I think all humans will use UTC, but it will probably take a populous moon or Mars colony, combined with the passage of a hundred years or more, for that behavioral change to take hold.