Time Zones

Probably one of the most frustrating things to deal with as a software developer is time. Specifically time zones and daylight savings time and all that nonsense. Tom Scott has a video about it but I have a recent story of my own.

This week at work, we lost about two days of effort trying to make our web application timezone-aware. Apparently, time zones are so ridiculously complicated, that it's basically impossible to store a time with an arbitrary time zone in your database while remembering what the time zone is.

Don't believe me?

The web stack in this story involves JavaScript, Python and PostgreSQL, with PostgresSQL's problems being applicable to any backend stack.

The Feature Request

At work, we're building a CMS to manage the content we publish. It's a standard CRUD web app that shows some web forms and puts things in the database.

On Monday the state of affairs was this: if you needed to input a time on the CMS (say, an Event Start Date), you had to be mindful to enter the time in Coordinated Universal Time (UTC) because the server doesn't know your local time and everything on its end is all UTC.

The users complained about this and wanted to be able to pick and choose time zones, so that they (in California) could enter that an event "starts at 10 AM in London" and be able to just enter "10 AM" and pick a time zone and have the server sort it all out.

This feature turned out to be way more frustrating of a task than you might think and we eventually had to admit defeat.

PostgreSQL TIMESTAMP WITH TIME ZONE

The database has to keep track of when things happen, like when each thing in the database was created or last updated. At the database side of things, with PostgreSQL anyway, you have a couple choices of data type to hold these dates.

TIMESTAMP will just take the exact timestamp you give it without doing anything. A timestamp in this case means something like 2001-02-03 13:30:56 -- a date and a time, but no hint of a time zone. TIMESTAMP columns are a bit dangerous to use, because there may be confusion about the exact time zone that a timestamp is in. For example, if one of your application servers had its local timezone set to US/Pacific and then you thought "maybe we should have the servers be in UTC" and changed it, then some of the timestamps in the database are Pacific and some are UTC and you have no idea what was what.

Or if your web application accepts a date/time from a user and stores that in the database, the time is probably in that user's time zone -- which you don't know -- adding more confusion.

PostgreSQL has a TIMESTAMP WITH TIME ZONE data type that keeps track of the time zone for you. When you query your database you might see a time stamp like 2018-08-02 18:45:32 +00:00 and you're like "there's the UTC offset!" and think you're good to go.

From the very beginning of our app we went with TIMESTAMP WITH TIME ZONE and I programmed it so any time the app auto-generated a timestamp (like a "created" and "updated" time), it would use datetime.utcnow() and store the UTC time.

Storing the UTC in the database is usually the best idea because it doesn't experience Daylight Saving Time, saving you from a whole class of bugs that can happen biannually when the system clock suddenly jumps an hour in the middle of the night while your cron scripts are running.

Front-end Date/Time Inputs

In the beginning, if the user was expected to enter a date/time on the CMS front-end, they had to enter a time in UTC format. This was for a couple of practical reasons. The HTML5 spec defines a few types of input boxes for dates and times. There is <input type="date"> and <input type="time">. The first one will yield values like 2018-08-02 and the other 18:34:22, but neither one will tell you the time zone.

When your <form> posts or JavaScript does it or whatever, the web server has no idea what your time zone is. The server and database, however, are configured to be in UTC time because that's a good idea, too, and the time you entered gets stored as UTC with no offset.

So if you entered a time thinking it was in your local time zone, the server can't tell that, and stores it as a UTC time, which will be way off from what you intended.

Aside: if you don't use the HTML5 date and time inputs, the other option is to do it "the old way" and find a fancy JavaScript calendar/time picker that will let the user choose the date/time and then put a string version in the text box.

JavaScript date/time pickers become problematic because they rarely integrate well with your front-end framework. There are jQuery plugins if your app uses jQuery, but those will not work if your app uses Angular, Vue or React because these frameworks use a "virtual DOM" and jQuery will be modifying elements "behind their back" and it just won't work out very well.

So you end up being limited to "what JavaScript calendar framework exists that uses my favorite front-end stack" and your options become limited. So I usually prefer to use the standard HTML5 types when I can get away with it.

Can't you detect the local time zone?

You can, in JavaScript, but not on the server side. In JavaScript, most modern browsers supporting the Intl API can get a good guess like America/Los_Angeles and for other browsers it's much harder.

So I'll start by telling you how we tried to work around this problem.

Our web front-end uses Vue.js and we had some datetime inputs already, using vue-datetime or some such. The existing Vue app would eventually get a string value like 2001-02-03 13:30:56 and send it to the Python back-end for the database, with no time zone, to be stored as UTC time.

So I thought a good solution to make this as painless as possible (as we have many time input fields throughout the CMS) was to create a custom Vue component which would wrap the existing vue-datetime component and also add a <select> box for the user to choose a time zone.

The user could click the datetime box and enter a date/time, then pick which time zone they intended it to be in, and the Vue component would yield a fully qualified timestamp like 2001-02-03 13:30:56 -05:00 with a UTC offset attached.

This much wasn't hard to do -- we already used moment.js to pretty up our timestamps for display, so we added moment-timezone to help us with the time zones -- by giving us a list of available zones and for determining that "US/Eastern" means a UTC offset of -05:00 except in the summer months when it's -06:00. So we had the Vue component up and running and giving us really good time stamps.

How to parse a UTC offset back into a named timezone?

Before leaving the front-end, we had to make sure that our fancy new <datetime-timezone> component would be able to parse a fully qualified timestamp and select the right timezone from the select box.

So when the database coughed back the timestamp of 2001-02-03 13:30:56 -05:00, the Vue component would automagically select "US/Eastern" in the timezone box instead of defaulting to your local timezone or whatever.

Apparently this is impossible. Multiple named time zones share the same offset.

What we could do with moment-timezone was to parse the time (with offset) and cast it to a specific timezone. From their docs:

var zone = "America/Los_Angeles";
moment.tz('2013-06-01T00:00:00',       zone).format(); // 2013-06-01T00:00:00-07:00
moment.tz('2013-06-01T00:00:00-04:00', zone).format(); // 2013-05-31T21:00:00-07:00
moment.tz('2013-06-01T00:00:00+00:00', zone).format(); // 2013-05-31T17:00:00-07:00

And so for our hacky way of parsing out that -05:00 might be Eastern Time, we just had to trial-and-error it. Something like,

// The timezones we care about.
var supportedZones = [
	"US/Pacific",
	"US/Mountain",
	"US/Central",
	"US/Eastern",
	// etc
]

// Try each one and see if the time gets mangled from the original.
var origTime = '2001-02-03T13:30:56-05:00';
for (var zone of supportedZones) {
	// If we cast the original time as this zone and it doesn't change the output
	// format, then this zone is compatible with the offset in the original time.
	if (moment.tz(origTime, zone).format() === origTime) {
		selectedTimeZone = zone;
		break;
	}
}

Now we had our datetime component able to create and re-parse fully qualified timestamps and it was time to move on to the back-end.

Python and SQLAlchemy

We're using SQLAlchemy as our database object mapper in the Python web app.

So the Python app was able to receive the fully qualified times from the front-end, including the UTC offset. We were hoping that we could put this into the database in a TIMESTAMP WITH TIME ZONE column and it would preserve the UTC offset and give it back to us later when queried.

But it didn't turn out this way.

When we gave the database a time with a -05:00 offset, the database ended up saving a time with a +00:00 offset -- UTC time. It did adjust the hours and date to fit the conversion from Eastern time to UTC, but it did not preserve the UTC offset value. So when the user reloads the page, the server sends it a UTC time with no offset and the front-end has no way to know what the original time zone was.

We Googled it and found this page explaining the way PostgreSQL and SQLAlchemy handle their dates and times.

Apparently, when you're talking about raw PostgreSQL, if you try and insert a fully qualified timestamp with offset, the database will silently strip off the offset -- completely disregarding it -- and taking the remaining date and time and interpreting it to mean the local time zone of the Postgres server.

So,

-- You wanted to store midnight Jan 1 in Eastern Time
UPDATE events SET start_date='2019-01-01 00:00:00 -05:00';

-- What actually happened:
-- 1. the UTC offset is stripped, so start_date='2019-01-01 00:00:00'
-- 2. Postgres interprets it as your local time zone (say US/Pacific -07:00)
-- 3. Postgres converts the US/Pacific time to UTC, mangling the hour offset

-- What actually got stored:
SELECT start_date FROM events ORDER BY id DESC LIMIT 1;
"2019-12-31 17:00:00 +00:00"

Which is not at all what you wanted.

SQLAlchemy actually helps us here -- it converts the date first using the UTC offset before handing it over to Postgres because it knows Postgres is going to otherwise disregard the time offset. But either way: once you've saved it in Postgres, you've lost information about the time that you can never get back again. The Python back-end and JavaScript front-end have no way of finding out the original time zone that the user saved in the database.

Options for workarounds?

At this point I could think of a few ways to work around this problem, and none of them sounded very good.

1. We could store the original named time zone in the database as a separate column from the timestamp. So we'd have a start_date TIMESTAMP WITH TIME ZONE and also a start_date_tz TEXT with a value like "US/Eastern"
   • This is ugly and not ideal because we have many time fields and we also have just so many columns already in general.
2. We could just always cast to the user's local time zone on page load.
   • If the user entered "10pm EST" and saved it and reloaded the page, it would come back as "7pm PST" if that was their local time zone. Still technically correct but it would confuse the user. You could see somebody being like "hey, I entered Eastern time, it says Pacific, let's just change it back" -- but naively changing the timezone would further mangle the time offset and make it incorrect, leading to even more confusion for the user.
3. We could try and remember the preference of the user on the front-end in HTML localStorage. So when the user said that timestamp right there on this page related to this table row, that was saved in Eastern time. So when that user reloads the page it would remember the timezone they picked, but a different user who loads the page would see it in their own local time zone by default. Not very ideal, either.
4. Server-side preferences? Same idea as localStorage but keep it in an arbitrary key/value store on the server side. But this still adds unnecessary complexity.

Ultimately we decided to just remove the <select> box for choosing a time zone and just force that all times on the front-end are in the user's own local time zone. If the user is in Pacific Time, all dates/times are displayed or entered in Pacific Time. If you're on the East coast, all dates/times are displayed in Eastern Time. This is a win over the original situation that all times needed to be UTC, but it still really sucks for usability.

Won't web browsers help?

In the HTML5 spec there were originally two more input types called <input type="datetime"> and <input type="datetime-local">, which were to implement timezone-aware date/time inputs (the latter used your system local time but would cough up the UTC offset as the field's value). But it seems they've changed their mind and pulled these out of the HTML5 spec.

Firefox for a brief time implemented datetime-local with a calendar pop-up when clicked, but they've removed that functionality now too, and these two input types just act as standard text fields with no special behavior.

It's a bit unfortunate because the web browser is a great place to handle these things, since it knows the user's local system time zone and it would save web developers from having to solve this problem again and again and again.

If I could burn it all down and start over

I'd probably not use any of the native TIMESTAMP types in the database. I'd instead make a custom type in SQLAlchemy that could hold onto a fully qualified timestamp (with offset) and also a named timezone that goes with it. In the database it would be stored as a JSON object in a standard TEXT field.

PostgreSQL's native datetime types are not up to the task, and I don't like the approach of having extra columns just to hold the time zones. If your web stack doesn't use Python but still has Postgres, you'll still run into the same problems. Other DB engines probably aren't much better: SQLite is way more fast-and-loose with data types (it's happy to let you store a string into an integer field, and retrieve it as a string, it doesn't care) and I wouldn't hold high hopes for MySQL either.