Loading Timezone Tables

The server uses the time zone tables to implement support for named times such as 'Africa\/Johannesburg'.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.

mysql> use mysql

Check the time_zone table and you will notice its empty

mysql> select * from time_zone;
Empty set (0.00 sec)

On Unix you can find the system time zone files located in /usr/share/zoneinfo. These files needs to be converted into a format that can be located into MySQL.

joseph@ubuntu:/usr/share/zoneinfo$ ls -l
total 324
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Africa
drwxr-xr-x 6 root root 20480 Jul 17 18:49 America
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Antarctica
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Arctic
drwxr-xr-x 2 root root 12288 Jul 17 18:49 Asia
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Atlantic
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Australia
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Brazil
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Canada
drwxr-xr-x 2 root root 4096 Jul 17 18:49 Chile

Convert system time zone to MySQL statements

Use the mysql_tzinfo_to_sql command line tool

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql mysql

and checking again after executing the above

mysql> select * from time_zone;
+--------------+------------------+
| Time_zone_id | Use_leap_seconds |
+--------------+------------------+
| 1 | N |

and from the time_zone_name table

mysql> select * from time_zone_name;
+----------------------------------------+--------------+
| Name | Time_zone_id |
+----------------------------------------+--------------+
| Africa/Abidjan | 1 |
| Africa/Accra | 2 |
| Africa/Addis_Ababa | 3 |

Note - You might need to restart the server touse the new time zone data.

Windows distriution comes preloaded with the time zone tables as it does not support time zone files for the operating system.