MySQL - Loading Time Zone Tables

The MySQL installation procedure creates a set of time zone tables in the mysql database. On Windows, the tables are part of the preinitialized mysql database. On Unix, the tables are created when mysql_install_db is executed, either automatically during RPM installation or manually if you install from a tar file. However, these time zone tables are created as empty tables, which means that, named time zones can not be used.

To enable named time zones, you must load the tables. This is an optional configuration procedure, but unless it is performed, time zone support is limited to SYSTEM zone and to numeric offsets such as '+06:00'.

On systems that have time zone files, it is best to use them for loading the MySQL time zone tables to ensure that the system and MySQL time zones are based on the same information. Many Unix systems have these files, often located under /usr/share/zoneinfo. For such system, use the mysql_tzinfo_to_sql program to convert the file contents into SQL statements that can be loaded into MySQL by the mysql program. If the files are located at /usr/share/zoneinfo, the command look like this:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Some systems have no time zone files, such as Windows and HP-UX. For these cases, MySQL provides a distribution a http://dev.mysql.com/downloads/timezones.html that contains a set of populated time zone tables (in form of MyISAM table files). You can download the distribution and install the files in your mysql database to replace the empty tables. Stop the server, copy the files to the mysql database directory, and restart the server.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License