A wise man once said that the safest way to store date and time values in MySQL is to store Unix timestamps in an unsigned INT column. I agree with Baron, but most of us have to deal with DATE, DATETIME, and/or TIMESTAMP values sometimes, and it’s often useful to know how to convert values from one time zone to another.

##The CONVERT_TZ() Function

You can convert a temporal value in MySQL from one time zone to another using the CONVERT_TZ() function. As an example, say I want to watch the Boston Marathon in Melbourne, Australia but I’m afraid it may be on after my bed time. The elite men start at 10:25 AM on April 18, 2016. Boston’s time zone will be EDT that day, which is UTC-4. Melbourne’s time zone that day will be AEST, which is UTC+10. The following query will tell me what time the Boston Marathon starts in Melbourne:

mysql> select convert_tz('2016-04-18 10:25','-04:00','+10:00');
+--------------------------------------------------+
| convert_tz('2016-04-18 10:25','-04:00','+10:00') |
+--------------------------------------------------+
| 2016-04-19 00:25:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

Therefore the marathon starts are 12:25 AM on Tuesday in Melbourne, which is past my bedtime.

It may seem a little silly to use CONVERT_TZ() with offsets like that rather than just adding 14 hours like this:

mysql> select '2016-04-18 10:25' + interval 14 hour;
+---------------------------------------+
| '2016-04-18 10:25' + interval 14 hour |
+---------------------------------------+
| 2016-04-19 00:25:00                   |
+---------------------------------------+
1 row in set (0.00 sec)

My argument for preferring CONVERT_TZ() is that it makes the query much more readable. In the first example it’s clear that I am converting a time from UTC-04:00 to UTC+10:00, whereas in the second example there’s no way to tell from the query why I’m adding 14 hours.

##Populating the Time Zone Tables

Using CONVERT_TZ() becomes more interesting using named time zones, but that only works if the system time zone tables are populated. For example if I try to find out what time the Boston Marathon starts in Melbourne using named time zones I may get a NULL value:

mysql> select convert_tz('2016-04-18 10:25','America/New_York','Australia/Melbourne');
+-------------------------------------------------------------------------+
| convert_tz('2016-04-18 10:25','America/New_York','Australia/Melbourne') |
+-------------------------------------------------------------------------+
| NULL                                                                    |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Why did I get NULL? Probably because my time zone tables are empty. Those tables are not populated by default.

Following the instructions in the reference manual I can populate those tables by running this command in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Now I can use named time zones:

mysql> select convert_tz('2016-04-18 10:25','America/New_York','Australia/Melbourne');
+-------------------------------------------------------------------------+
| convert_tz('2016-04-18 10:25','America/New_York','Australia/Melbourne') |
+-------------------------------------------------------------------------+
| 2016-04-19 00:25:00                                                     |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Bear in mind that time zone rules change sometimes, so you may want to review the manual and develop a plan to stay up to date with those changes.

##Know What Time Zone MySQL is Using

My examples so far have used static datetime values. When dealing with values stored in a table or returned by MySQL functions it’s important to know the context of those values if I want to convert them. For example I need to know what time zone MySQL is using if I want to convert a datetime value stored in a table, or the output of a function like NOW() to a different time zone.

The relevant session and global variables are named time_zone and system_time_zone. You can access them like this:

mysql> select @@session.time_zone,@@global.time_zone,@@global.system_time_zone;
+---------------------+--------------------+---------------------------+
| @@session.time_zone | @@global.time_zone | @@global.system_time_zone |
+---------------------+--------------------+---------------------------+
| SYSTEM              | SYSTEM             | UTC                       |
+---------------------+--------------------+---------------------------+
1 row in set (0.00 sec)

If the time_zone variable is set to SYSTEM then the system_time_zone will be used.