MariaDB starting with 10.4.6

From MariaDB 10.4.6, mariadb-tzinfo-to-sql is a symlink to mysql_tzinfo_to_sql, the tool for loading time zones on systems that have a zoneinfo database.

MariaDB starting with 10.5.2

From MariaDB 10.5.2, mysql_tzinfo_to_sql is the symlink, and mariadb-tzinfo-to-sql the binary name.

mariadb-tzinfo-to-sql is a utility used to load time zones on systems that have a zoneinfo database to load the time zone tables (time_zone, time_zone_leap_second, time_zone_name, time_zone_transition and time_zone_transition_type) into the mysql database.

Most Linux, Mac OS X, FreeBSD and Solaris systems will have a zoneinfo database - Windows does not. The database is commonly found in the /usr/share/zoneinfo directory, or, on Solaris, the /usr/share/lib/zoneinfo directory.


mariadb-tzinfo-to-sql can be called in several ways. The output is usually passed straight to the mariadb client for direct loading in the mysql database.

shell> mariadb-tzinfo-to-sql timezone_dir
shell> mariadb-tzinfo-to-sql timezone_file timezone_name
shell> mariadb-tzinfo-to-sql --leap timezone_file

Resetting timezone tables

If there is a need to reset the timezone to the default, to before using mariadb-tzinfo-to-sql, one can do that by executing:

truncate table mysql.time_zone;
truncate table mysql.time_zone_name;
truncate table mysql.time_zone_transition;
truncate table mysql.time_zone_transition_type;
truncate table mysql.time_zone_leap_second;

The old timezone values will be in effect until the server is restarted.


Most commonly, the whole directory is passed:

shell>mariadb-tzinfo-to-sql /usr/share/zoneinfo | mariadb -u root mysql

Load a single time zone file, timezone_file, corresponding to the time zone called timezone_name.

shell> mariadb-tzinfo-to-sql timezone_file timezone_name | mariadb -u root mysql

A separate command for each time zone and time zone file the server needs is required.

To account for leap seconds, use:

shell> mariadb-tzinfo-to-sql --leap timezone_file | mariadb -u root mysql

After populating the time zone tables, you should usually restart the server so that the new time zone data is correctly loaded.


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.