Calculating timezone offsets

Time zones are a tricky feature. You live in a given time zone, and most of the time you won’t think about that at all. You may live in a place where you are conscious of time zones, such as the United States, if your business spans across the country, where you know that New York is three hours ahead of San Francisco or Chicago and Dallas share the same time zone. Time Zone support in MySQL is a complicate business in itself. Once you have updated your time zone tables, you can set your time zone in an human readable format:

set global time_zone="America/Los_Angeles";

This is nice and well. It tells you which time zone your server is working with. However, things get a bit hairy when you need to do practical things with this information. Suppose you are looking at a server that displays unfamiliar time zones:

mysql> select @@system_time_zone, @@time_zone;
+--------------------+-----------------+
| @@system_time_zone | @@time_zone     |
+--------------------+-----------------+
| GST                | Asia/Singapore  |
+--------------------+-----------------+
1 row in set (0.00 sec)

When you run select now() in this environment, you get the time in “Asia/Singapore” time zone, although the operating system is running in Dubai time zone.

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-08-14 12:00:18 |
+---------------------+
1 row in set (0.00 sec)

A look at your watch can tell you what’s the difference. But if you need to put that information into a calculation, then you need a way of converting that string “Asia/Singapore” into a number:

mysql> select timediff( now(), utc_timestamp());
+-----------------------------------+
| timediff( now(), utc_timestamp()) |
+-----------------------------------+
| 08:00:00                          |
+-----------------------------------+
1 row in set (0.00 sec)

This is the time zone offset in this server, as referred to Greenwich Mean Time (GMT) or UTC. It means that Singapore is 8 hours ahead of GMT. You could get the same result by asking

mysql> select timediff( now(), convert_tz(now(), @@time_zone, 'UTC'));
+-----------------------------------+
| timediff( now(), utc_timestamp()) |
+-----------------------------------+
| 08:00:00                          |
+-----------------------------------+
1 row in set (0.00 sec)

OK. This is longer, but it has the ability of becoming a general purpose time zone offset calculator:

mysql> select timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'UTC'));
+-------------------------------------------------------------------+
| timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'UTC')) |
+-------------------------------------------------------------------+
| -07:00:00                                                         |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'Europe/Rome'));
+---------------------------------------------------------------------------+
| timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'Europe/Rome')) |
+---------------------------------------------------------------------------+
| -09:00:00                                                                 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'Asia/Singapore'));
+------------------------------------------------------------------------------+
| timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'Asia/Singapore')) |
+------------------------------------------------------------------------------+
| -15:00:00                                                                    |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)