If you want to use named time zones in MariaDB, you’ll need to make sure they’ve been configured.

By “named time zones”, I mean being able to use strings like America/Los_Angeles instead of −08:00 or −07:00 when specifying the time zone. For example, when using the CONVERT_TZ() function.

Here’s how to configure named time zones in MariaDB.

Time Zone Tables

MariaDB has the following time zone tables in the mysql database:

• time_zone
• time_zone_leap_second
• time_zone_name
• time_zone_transition
• time_zone_transition_type

By default, these tables are created, but not populated.

To use named time zones, you need to populate these tables. The method for doing this will depend on your system (i.e. whether or not your system actually contains time zone info).

Unix-Like Systems

The reason the above time zone tables are empty by default is because it’s usually better that the system handles the time zone, if possible.

Most Unix-like systems such as Linux, Mac OS X, FreeBSD, and Solaris have a zoneinfo database. This zoneinfo database can be loaded into the time zone tables in MariaDB with the mysql_tzinfo_to_sql utility.

If your system contains a zoneinfo database it’s preferable that you use this method to populate the time zone tables. Otherwise you may cause a difference in datetime handling between MariaDB and other applications on your system.

To load the time zone tables, open a terminal window and run the following:

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

Enter the password for the root user. If you get an “access denied” error, see this fix.

That’s it. The time zone tables should now be populated.

In my case I got one warning:

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

This warning can be safely ignored.

This warning is probably due to the fact that Unix-like systems don’t include leap seconds. That is, Unix-like systems have no way to represent the leap second in the form of 23:59:60. Instead it just uses the same second twice.

This adheres to the POSIX (Portable Operating System Interface) standard, which requires that leap seconds be omitted from reported time.

Windows, HP-UX Systems

Some systems, such as Windows and HP-UX don’t have a zoneinfo database, so you will need to load the time zone tables via SQL script if you’re using these operating systems.

See the MySQL documentation for the scripts and installation instructions.

Check the Time Zone Tables

Once the time zone tables have been populated, we can run a quick query to check that they are in fact, populated.

Example:

SELECT * 
FROM mysql.time_zone_name
LIMIT 10;

Result:

+--------------------+--------------+
| 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 |
+--------------------+--------------+

In this case I selected the top 10 results from the time_zone_name table.

Here’s the full count:

SELECT COUNT(*) 
FROM mysql.time_zone_name;

Result:

+----------+
| COUNT(*) |
+----------+
|      594 |
+----------+

You can run through the other tables as required.