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.
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.
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.
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
andtime
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.
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.
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.
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.
At this point I could think of a few ways to work around this problem, and none of them sounded very good.
start_date TIMESTAMP WITH TIME ZONE
and also a start_date_tz TEXT
with a value like "US/Eastern"
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.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.
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.
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.
There are 0 comments on this page. Add yours.
0.0200s
.