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' );