How to Set Up Named Time Zones in MySQL

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

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

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

Check Your System

Before jumping in, it’s probably a good idea to check whether or not your system has already been configured for named time zones.

To do this, run the following query:

SELECT * 
FROM mysql.time_zone_name
LIMIT 10;

Result:

Empty set (0.00 sec)

If your system has already been configured for named time zones, the above query will return a list of named time zones. In my case the query returned no results. This is because my system hasn’t yet been configured for named time zones.

Also, running queries like the following will return NULL if named time zones haven’t been configured:

SELECT CONVERT_TZ('2035-10-03 10:30:45','Europe/Helsinki','UTC');

Result:

NULL

Here, I tried to use the CONVERT_TZ() function to convert a date/time value to another time zone using named time zones, but named time zones hadn’t yet been configured on my system.

When I talk about named time zones not being “configured”, I mean the time zone tables haven’t been populated yet.

Time Zone Tables

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

Table	Description
`time_zone`	Time zone IDs and whether they use leap seconds.
`time_zone_leap_second`	When leap seconds occur.
`time_zone_name`	Mappings between time zone IDs and names.
`time_zone_transition`	Time zone descriptions.
`time_zone_transition_type`	Time zone descriptions.

The MySQL installation process creates the above time zone tables, but it does not populate them with any data.

The first query that I ran above queried the time_zone_name table. We didn’t get an error because the table already exists. However, no rows were returned by the query, which means that the time_zone_name table contains no rows (i.e. it hasn’t yet been populated).

To use named time zones, you need to populate the above 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 MySQL with the mysql_tzinfo_to_sql utility. The mysql_tzinfo_to_sql utility reads the system’s time zone files and creates SQL statements from them. MySQL then uses those statements to load the time zone tables.

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 MySQL 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 possible fix.

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

In my case I got some warnings:

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

Regardless, the tables were populated and I was able to use functions such as CONVERT_TZ() with named time zones.

The warnings are about time zones the utility found on the system that it doesn’t recognise as containing usable time zone info.

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 (on my system):

+----------+
| COUNT(*) |
+----------+
|      598 |
+----------+

We can now go ahead and use functions like CONVERT_TZ() with named time zones:

SELECT CONVERT_TZ('2035-10-03 10:30:45','Europe/Helsinki','UTC');

Result:

2035-10-03 07:30:45