Loading Timezone Data into MySQL

When working in MySQL you might find yourself dealing with dates, times, and timezones. I was recently working on something involving timezone conversions and my queries were returning no data at all, despite knowing the records were there.

Fortunately I had hit this problem before, and I knew straight away what it was — missing timezone data.

The Symptom

If your query contains something like this:

CONCAT(
    SUBDATE(
        DATE(
            CONVERT_TZ(users.created_at, "UTC", 'Europe/London')
        ),
        WEEKDAY(
            DATE(
                CONVERT_TZ(users.created_at, "UTC", 'Europe/London')
            )
        )
    ),

The CONVERT_TZ calls will silently fail when MySQL has no reference for what those timezone names mean — returning NULL rather than an error, which is why queries appear to return nothing. The fix is to load timezone data into MySQL.

The official documentation is here: dev.mysql.com/doc/refman/8.4/en/time-zone-support.html — but here's the short version for Ubuntu and WSL2.

Step 1: Install Timezone Data

First, make sure your machine has timezone data installed:

sudo apt-get install tzdata

Step 2: Load the Data into MySQL

Next, run the following command to pipe the timezone data from your system into MySQL's own tables:

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

You will be prompted for the root database password. The import should only take a few moments.

Step 3: Confirm It Worked

Once the import is complete, verify the data is present by running the following in your SQL browser or client:

USE mysql;
SELECT * FROM time_zone_name LIMIT 10;

You should see timezone records in the results:

Now when you run your queries, CONVERT_TZ will correctly resolve named timezones and return the data you expect.