The trouble with travel

• 11.09.2022 22:28 UTC+02:00
• [ perma(?)link ]
• < previous | next >

Until now, all posts that I've made to this blog have been from the same time zone as the server. So, all I have to do to record the date and time of a post, is to set the default value in the datetime column to CURRENT_TIMESTAMP(). Easy peasy lemon squeezy.

But that changed with my recent sojourn to the land of nanny-knows-best—the UK—which is an hour behind the time here in Switzerland. I now have to concern myself with time zone differences, because CURRENT_TIMESTAMP() may well reflect the time when the post was inserted into the database, but not the local time when it was written.

I won't bore you with the details, because they're dull and it'll inevitably show up the gaps in my understanding. But I want to dispel a couple of misconceptions that I've come across on coding forums, before explaining the code behind this miracle.

Myth vs reality

Standards matter

The first misconception is that UTC is the same as GMT. It isn't. UTC is a time standard, while GMT is a time zone, and the two may differ slightly. This is important when precision in global synchronisation is a priority; not so much for my purposes, but a standard is a standard.

As a standard time reference, UTC never changes and is unaffected by daylight savings—like GMT, but see above. This means that CET is UTC+01:00 and CEST is UTC+02:00. During the winter, the UK is on GMT/UTC+00:00, but on BST/UTC+01:00 in the summer.

Changing times

The second misconception is that correcting time zone differences by reference to UTC offset values is the best approach. Once again, it isn't.

This is because time zone offsets change with daylight savings, and not all countries apply them. Even those that do, don't all change in synchrony. So the best approach is to reference time zone names—such as Europe/London and Americas/New_York—which change along with their relevant geographical area. These have to be converted to the UTC offset programmatically, since I couldn't find a handy-dandy look-up table.

The code

After scouring the likes of StackExchange, to pick the minds of more knowledgeable developers than myself, I adapted my previous SQL code. I created a new table called timezones with two columns, an incremental counter—ID—and the named time zones—TZ.* The posts table has an additional column—TZ with data type smallint, length 6, and default of NULL—which is joined to the timezones.id column.

Okay, let's now pull this apart.

The most important function here is CONVERT_TZ(), which converts a unix timestamp—datetime in this instance—from one timezone to another:

I want to use this to convert a post's timestamp from its timezone—either posts.TZ or @@SESSION.time_zone—to UTC. The difference between this value and UTC+00:00 gives the UTC offset, which is then formatted by TIME_FORMAT() to HH:MM.

For posts made in the same time zone as the server, the code is quite straightforward:

It converts datetime to UTC, and then takes the difference between that and the stored timestamp value to give the offset. The code for posts in other time zones is a little more complex though.

The conversions highlighted in orange and blue take the stored timestamp, and convert it from the server time zone to the local one. The one highlighted in green converts the blue instance to UTC. The difference is the offset from UTC.

There's one final hurdle to overcome. TIMEDIFF() prepends negative offsets with -, but positive ones are returned without +. That's fixed with a call to PHP's SUBSTR() function. I thought about doing this in SQL, but it was getting messy (see below).

It seems complicated, and there may well be a better way of achieving the same result. But this is mine, and it works. That's good enough.

(nerd)

Positivity

Here's that more straightforward code, above, adapted to return positive offsets prepended with +:

See what I mean about messy? In contrast, the PHP code looks like this:

It determines whether the first character is a minus, and if not it prepends a plus. Simple.

(thumbup)