MySQL CONVERT_TZ returns null

Posted September 30th, 2009 in MySql

MySQL's CONVERT_TZ function converts datetime values from one timezone to another. If the timezones haven't been set up in the MySQL database then the value returned from the function will be null. This post shows how to fix the problem.

Example Query

The following query converts the current date and time from New Zealand time to GMT:

SELECT CONVERT_TZ(NOW(),'Pacific/Auckland','GMT')

At the time of writing this post the current time was 10:01am on Sunday September 21st in Auckland. At that time, the above function call should return:

2009-09-19 22:01:48

However, if the timezone information hasn't been saved to the MySQL database then the function will return NULL instead. If queries aren't working as expected when using this function this may be the reason why. Simply running the above query and seeing the output will tell you whether or not the timezone information has been saved.

Fixing the issue

I've covered how to load the MySQL timezone information before on a *NIX box in a post about setting the timezone per connection and cover the same information here.

If you have sufficient rights on the server, you can simply issue a command like this from the command line, assuming your zoneinfo files are at /usr/share/zoneinfo (they are on a CentOS,  Red Hat Enterprise Linux, Debian setup):

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

This will load the timezone names and offsets into the "mysql" database and you can view the names by querying the "time_zone_name" table.

Note that you should run this regularly to rebuild the timezone database because timezone information does change over time. The SQL queries run truncate data from the appropriate tables so each time you run the full set of data is updated and the old records purged.