mysql_tzinfo_to_sql - Load the Time Zone Tables in MySQL

The mysql_tzinfo_to_sql program helps load the time zone tables in the mysql database. It is used on systems which have a zoneinfo database, i.e the set of files that describe the time zones. Examples of such systems include Linux, FreeBSD, Solaris, and macOS. One most probable location for these files is the /usr/share/zoneinfo directory (/usr/share/lib/zoneinfo on Solaris).

Invoking mysql_tzinfo_to_sql

If the system doesn’t have a zoneinfo database, the downloadable package can be installed. The mysql_tzinfo_to_sql can be invoked in many ways. Some of them have been shown below −

shell> mysql_tzinfo_to_sql tz_dir
shell> mysql_tzinfo_to_sql tz_file tz_name
shell> mysql_tzinfo_to_sql --leap tz_file

In the first invocation syntax, the zoneinfo directory path name is passed to the mysql_tzinfo_to_sql. The output is sent to the mysql program.

Let us take an example to understand this −

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

mysql_tzinfo_to_sql reads the user’s system's time zone files and generates SQL statements from them. Then, mysql processes these statements so as to load into the time zone tables.

The second invokation causes mysql_tzinfo_to_sql to load a single time zone file tz_file which corresponds to a time zone name tz_name as shown below −

shell> mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql

If the user’s time zone needs to account for leap seconds, mysql_tzinfo_to_sql can be invoked with the help of the third syntax. This third syntax initializes the leap second information. The tz_file is the name of your time zone file −

shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql

After running mysql_tzinfo_to_sql, it is suggested to restart the server so that it doesn’t continue to use previously cached time zone data.

Updated on: 10-Mar-2021


Kickstart Your Career

Get certified by completing the course

Get Started