{"id":12803,"date":"2023-12-09T20:44:33","date_gmt":"2023-12-10T03:44:33","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12803"},"modified":"2023-12-09T21:32:17","modified_gmt":"2023-12-10T04:32:17","slug":"load-time-zone-tables","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/load-time-zone-tables\/","title":{"rendered":"How to Load Time Zone Tables"},"content":{"rendered":"\n

Summary<\/strong>: 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.<\/p>\n\n\n\n

Loading time zone tables in MySQL is crucial for accurate handling of date and time information, especially when dealing with global applications.<\/p>\n\n\n\n

The mysql<\/code> system database comprises tables designed to store time zone-related data. However, these tables do not contain any data by default.<\/p>\n\n\n\n

To make some of the date time-related functions<\/a> that deal with the time zone<\/a> work properly, you need to manually load time zone data into these tables.<\/p>\n\n\n\n

Checking time zone tables<\/h2>\n\n\n\n

First, open the Command Prompt on Windows or terminal on macOS and Linux.<\/p>\n\n\n\n

Next, connect to the MySQL server<\/a> using the mysql<\/code> program:<\/p>\n\n\n

mysql -u root -p<\/code><\/span><\/pre>\n\n\n
Then, switch the current database to mysql<\/code>:<\/p>\n\n\n
use<\/span> mysql<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, show the time zone tables:<\/p>\n\n\n
show tables like 'time_zone%'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------------------+\r\n| Tables_in_mysql (time_zone%) |\r\n+------------------------------+\r\n| time_zone                    |\r\n| time_zone_leap_second        |\r\n| time_zone_name               |\r\n| time_zone_transition         |\r\n| time_zone_transition_type    |\r\n+------------------------------+\r\n5<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, retrieve data from the time_zone_name<\/code> table:<\/p>\n\n\n
select * from<\/span> time_zone_name;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Empty set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Loading time zone data on Windows<\/h2>\n\n\n\n
First, download the timezone package here<\/a>.<\/p>\n\n\n\n
Second, extract the downloaded zip file to a directory e.g., C:\\temp\\tz<\/code>. The tz<\/code> directory includes the timezone_posix.sql<\/code> file.<\/p>\n\n\n\n
Third, open the Command Prompt and connect to the MySQL server:<\/p>\n\n\n
mysql -u root -p<\/code><\/span><\/pre>\n\n\n
Fourth, switch the current database to mysql<\/code>:<\/p>\n\n\n
use<\/span> mysql<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, load the time zone data into the time zone tables using the source<\/code> command:<\/p>\n\n\n
source<\/span> c<\/span>:\\temp<\/span>\\tz<\/span>\\timezone_posix<\/span>.sql<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you need to replace the path to the timezone_posix.sql<\/code> with your path.<\/p>\n\n\n\n
The source<\/code> command will run the SQL statements in the file timezone_posix.sql<\/code> to insert data into the time zone tables.<\/p>\n\n\n\n
Sixth, check the time_zone<\/code> table:<\/p>\n\n\n
select count(*) from<\/span> time_zone;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll show 597 rows, which corresponds to 597 time zones.<\/p>\n\n\n\n
net stop mysql\nnet start mysql<\/code><\/span><\/pre>\n\n\n
Note that you need to replace the mysql<\/code> with your actual MySQL service name.<\/p>\n\n\n\n
Loading time zone data on macOS and Linux<\/h2>\n\n\n\n
macOS and Linux come with built-in time zone data stored in the \/usr\/share\/zoneinfo<\/code> directory.  <\/p>\n\n\n\n
To load this time zone data into MySQL, you use the mysql_tzinfo_to_sql<\/code> utility program that comes by default with the MySQL installation.<\/p>\n\n\n\n
To load the time zone tables, you follow these steps:<\/p>\n\n\n\n
First, open the terminal.<\/p>\n\n\n\n
Next, run the following command:<\/p>\n\n\n
mysql_tzinfo_to_sql \/usr\/share\/zoneinfo | mysql -u root -p mysql<\/code><\/span><\/pre>\n\n\n
It’ll prompt you to enter a password for the root<\/code> account and start loading the time zones.<\/p>\n\n\n\n
Then, connect to the MySQL server:<\/p>\n\n\n
mysql -u root -p<\/code><\/span><\/pre>\n\n\n
After that, switch the current database to the mysql<\/code> system database:<\/p>\n\n\n
use<\/span> mysql<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, retrieve data from the time_zone<\/code> table:<\/p>\n\n\n
select count(*) from<\/span> time_zone;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------+\n| count(*) |\n+----------+\n|      1793<\/span>|\n+----------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns 1793 time zones.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the source<\/code> command to load time zone data from the provided timezone package.<\/li>\n\n\n\n
  • Use the mysql_tzinfo_to_sql<\/code> utility program to load time zone data into the time zone tables in the MySQL database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Finally, restart the MySQL server<\/a> to apply the new time zones:<\/p>\n\n\n