How to set named time zones in MariaDB

Naming a time zone means using the name of the time zone, not the hour difference from standard time. For example Asia/Chongqing is the name of a time zone, and +08:00 is not a name of the time zone.

In MariaDB, you don’t have direct access to named timezones by default. To use them, you must first configure them.

Here’s how to configure named timezones in MariaDB.

Time zones table

Like MySQL, the mysql database has the following time zone tables:

time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type

These tables are created with MariaDB installation, but they are empty. You need to populate these tables before you can use them.

To populate these tables, proceed according to the operating system.

If your system contains a zoneinfo file, such as Mac OS, Linux, FreeBSD, Sun Solaris, please use the mysql_tzinfo_to_sql utility to read and populate the mysql.time_zone* tables from the zoneinfo file.

If your system does not contain zoneinfo files, such as Windows, please go to https://dev.mysql.com/downloads/timezones.html and download the corresponding files and fill them into the mysql.time_zone* tables.

Import timezones from zoneinfo

If your system is a Unix-like system, such as Mac OS, Linux, FreeBSD, Sun Solaris, please run the following command to load the time zone tables into the mysql database:

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

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

Enter the password of root user, and the time zone tables will be populated immediately.

You may receive a warning:

Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.

You can ignore this warning. This is due to the fact that Unix-like systems do not contain leap seconds, however, this is POSIX (Portable Operating System Interface) compliant.

Import from sql scripts provided by MySQL

For Windows system, since there is no zoneinfo database, you need to load the time zone table through SQL script. Follow the steps below:

Download the SQL script: https://downloads.mysql.com/general/timezone_2022g_posix_sql.zip.
Unzip the file you just downloaded and you’ll get a file: timezone_posix.sql.
Login to MariaDB with the following command:
```
.\mysql.exe -u root -p
```
Enter the root user password and confirm.
Connect to MySQL database
```
USE mysql
```

Import data from SQL script file:

SOURCE C:\Users\Adam\Downloads\timezone_posix.sql

Use time zone table

After importing the time zone information through the two above methods, you can query mysql.time_zone_name to check whether the import is correct.

SELECT *
FROM mysql.time_zone_name;

Output:

+----------------------------------+--------------+
| Name                             | Time_zone_id |
+----------------------------------+--------------+
| Africa/Abidjan                   |            1 |
| Africa/Accra                     |            2 |
| Africa/Addis_Ababa               |            3 |
| Africa/Algiers                   |            4 |
| Africa/Asmara                    |            5 |
| Africa/Asmera                    |            6 |
| Africa/Bamako                    |            7 |
| Africa/Bangui                    |            8 |
| Africa/Banjul                    |            9 |
| Africa/Bissau                    |           10 |
...
...
| Zulu                             |          597 |
+----------------------------------+--------------+
597 rows in set (0.000 sec)

Conclusion

This article describes different ways to populate the MariaDB time zone tables on different systems.