How to Load Time Zone Tables

Summary: in this tutorial, you will learn how to load time zone data into the time zone tables in MySQL server on Windows, macOS, and Linux.

Loading time zone tables in MySQL is crucial for accurate handling of date and time information, especially when dealing with global applications.

The mysql system database comprises tables designed to store time zone-related data. However, these tables do not contain any data by default.

To make some of the date time-related functions that deal with the time zone work properly, you need to manually load time zone data into these tables.

Checking time zone tables

First, open the Command Prompt on Windows or terminal on macOS and Linux.

Next, connect to the MySQL server using the mysql program:

mysql -u root -p

Then, switch the current database to mysql:

use mysql;Code language: PHP (php)

After that, show the time zone tables:

show tables like 'time_zone%';Code language: JavaScript (javascript)

Output:

+------------------------------+
| Tables_in_mysql (time_zone%) |
+------------------------------+
| time_zone                    |
| time_zone_leap_second        |
| time_zone_name               |
| time_zone_transition         |
| time_zone_transition_type    |
+------------------------------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

Finally, retrieve data from the time_zone_name table:

select * from time_zone_name;Code language: JavaScript (javascript)

Output:

Empty set (0.00 sec)Code language: JavaScript (javascript)

Loading time zone data on Windows

First, download the timezone package here.

Second, extract the downloaded zip file to a directory e.g., C:\temp\tz. The tz directory includes the timezone_posix.sql file.

Third, open the Command Prompt and connect to the MySQL server:

mysql -u root -p

Fourth, switch the current database to mysql:

use mysql;Code language: PHP (php)

Fifth, load the time zone data into the time zone tables using the source command:

source c:\temp\tz\timezone_posix.sqlCode language: CSS (css)

Note that you need to replace the path to the timezone_posix.sql with your path.

The source command will run the SQL statements in the file timezone_posix.sql to insert data into the time zone tables.

Sixth, check the time_zone table:

select count(*) from time_zone;Code language: JavaScript (javascript)

It’ll show 597 rows, which corresponds to 597 time zones.

Finally, restart the MySQL server to apply the new time zones:

net stop mysql
net start mysql

Note that you need to replace the mysql with your actual MySQL service name.

Loading time zone data on macOS and Linux

macOS and Linux come with built-in time zone data stored in the /usr/share/zoneinfo directory.

To load this time zone data into MySQL, you use the mysql_tzinfo_to_sql utility program that comes by default with the MySQL installation.

To load the time zone tables, you follow these steps:

First, open the terminal.

Next, run the following command:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

It’ll prompt you to enter a password for the root account and start loading the time zones.

Then, connect to the MySQL server:

mysql -u root -p

After that, switch the current database to the mysql system database:

use mysql;Code language: PHP (php)

Finally, retrieve data from the time_zone table:

select count(*) from time_zone;Code language: JavaScript (javascript)

Output:

+----------+
| count(*) |
+----------+
|      1793|
+----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

It returns 1793 time zones.

Summary

• Use the source command to load time zone data from the provided timezone package.
• Use the mysql_tzinfo_to_sql utility program to load time zone data into the time zone tables in the MySQL database.