24.6. 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.

The server uses the time zone tables to implement support for named time zones such as 'Europe/Warsaw'. However, the time zone tables are created as empty tables, which means that, by default, named time zones cannot be used. To enable this capability, you must load the tables. This is an optional configuration procedure, but unless it is performed, time zone support is limited to the SYSTEM zone and to numeric zone offsets such as '+06:00'.

On operating systems that have their own 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 systems, 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 looks 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 AB provides a distribution at http://dev.mysql.com/downloads/timezones.html that contains a set of populated time zone tables (in the form of MyISAM table files). You can download the distribution and install the files in your mysql database to replace the empty time zone tables. Stop the server, copy the files to the mysql database directory, and restart the server.