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 bedtime. 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:
Therefore the marathon starts at 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:
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:
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:
Now I can use named time zones:
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:
If the time_zone variable is set to SYSTEM then the system_time_zone will be used.