Random Notes

Coordinated Universal Time (UTC) is one of the greatest helps to programmers when dealing with datetime values. UTC makes my life easier, and thus raises my quality of life. Yet, it is plagued by a fallacy that I've seen many times in programmers' discussions online. This is the UTC everywhere fallacy. Even for all the nice things UTC gives us, it just doesn't solve everything.

Most developers are already aware that storing future dates as UTC has problems. In a nutshell, timezone rules may change between the point when a future time is stored and when it is read and converted back to the user's timezone. This can lead to the stored time being converted into a different one that was originally inserted.

But less known is that UTC may not be enough for storing past datetimes either. Indeed, just today I've read comments on Hacker News, that I quote:

I think summary here is that if you are storing an exact moment in time then UTC does the job.

The rules I follow: 1. If you're recording an event in the past, store it in UTC.

When the event [is] in the past you know which TZ rules to apply for conversion.

To illustrate the problem, let's get to know Carol. Carol is a programmer who lives in Finland and the Europe/Helsinki timezone. The UTC offset is +2 or +3 hours, depending on the time of year – Carol hates daylight savings, but that's a subject for a different post.

Carol loves statistics, so she uses a service that tracks her programming habits, and most importantly for this post, the amount of "experience points" she has for every hour of the day. Thus she can keep tabs on her most productive times of day.

The developer of the service decides to store the experience accumulating events with UTC datetimes. The data in the database looks like this:

Time	Points
2022-03-10T10:00:00Z	15
2022-03-10T10:15:03Z	20
2022-03-10T11:01:15Z	13

So far so good. Carol checks her profile and sees that she has gained 35 points at noon (12) and 13 points at 13 o'clock. The service knows to convert her datetimes back to Europe/Helsinki, the data is correct, and all is well. With time, Carol accumulates data, and the service charts her data like this:

One day Carol gets an offer she cannot refuse. She moves to Japan to start in a new, fancy job. The timezone is Asia/Tokyo, UTC+9 year round. Among the changes in her life, she sets up her new location in all her online services. As she views her programming statistics, she's confused.

What happened? Carol is sure she didn't program that late in the evening. Unfortunately, the choice to use UTC has created a problem. When loading the datetimes from the database, they are converted to the user's timezone. But the user's timezone has changed, and now the conversions result in different times. They're still referring to the same moments in time, but because the user has the additional context of where they were at the time, the results look wrong to them.

What's the solution? In this case just storing the original UTC offset along with the UTC datetime would suffice. Instead of converting to the user's current timezone, the datetimes would be converted using the stored UTC offset. The chart would then show the user's local time – from the time of insertion – no matter where they move to. When dealing with such aggregated analytics, this is a requirement. But it would apply even with single events: if you move to a different place, you'd still be surprised to see your previous 6 o'clock morning run being now displayed as a 13 o'clock afternoon run. Indeed, there was a real world example on Hacker News:

One that really annoys me is the Health app on iPhone, where it records step count and other data, and does it well if you stay in the same timezone. But when you change timezone, the data is shown in the zone you’re in now (I assume it’s stored in UTC and then converted to the current timezone). Which is technically correct, but then looking back, it looks like I was doing a whole lot of walking from 9pm to 11am for a few weeks while I was on a trip (it was actually just a regular daily schedule), which then messes up the daily average step count, because the day boundaries aren’t what they actually were there.

UTC is great, and solves your problems 9 out of 10 times. But 9 out of 10 still leaves out many situations. So before you store all your datetimes as UTC, think of your use case. Even if you're being technically correct, you may end up with a nonintuitive user experience. And if someone online – possibly with the nickname Nicd – mentions storing local datetimes in the database, please think a bit before launching into a rant about how wrong they are. 😅 Seriously, this is a discussion I've had to have several times.

Oh, and don't get me started on timestamp with time zone. But that's another blog post again.