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.