Tuesday, January 3, 2012

Set MySql Timezone information on Linux

In order to use the MySQL CONVERT_TZ() function on Linux which converts a datetime from one timezone to another, MySql needs timezone definitions and offsets which is dependent on the operating system in use. Otherwise CONVERT_TZ() returns NULL values.

To do this on Linux, use MySQL's utility script, "mysql_tzinfo_to_sql".

Example usage:
mysql_tzinfo_to_sql /usr/share/zoneinfo 
This returns a sql script to run to setup the timezone information. The following will setup the timezone information directly:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Now you can view your timestamps in local timezone:
SELECT CONVERT_TZ('2010-01-02 00:00:00', 'UTC', 'America/Chicago' );