Loading MySQL Time Zone Data

When working in MySQL you might find yourself dealing with the dreaded dates, times and timezones. I was recently working on something involving timezones and my queries were returning no data at all, despite me knowing there was data present in the database to return. Fortunately, I had hit this problem before on an older laptop, and I realised that I was missing time zone data.

Your query contain something like this:

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

In this case, the timezone conversions are going to fail because the database has no reference for what these timezones mean. So, to fix this, we need to give MySQL some timezone data.

At the time of writing, the most recent documentation for this is here: https://dev.mysql.com/doc/refman/8.4/en/time-zone-support.html - but to Tl;Dr it for you; you must load the timezone data from your machine and pass it into MySQL.

As I use WSL2 and Ubtuntu this guide will cover how do to do this on Ubuntu.

Firstly, you need to make sure you have the timezone data installed on your machine, the easiest way to do this is to run the following command

sudo apt-get install tzdata

This will install the neccesary timezone data. Next, we need to run a command to tell MySQL to load the data into it's database tables:

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

This will ask your for the root password for your database user and then start inserting the data. This should only take a few moments. Once it is done, confirm the data is present by running the following SQL in your favourite SQL browser:

USE mysql;
SELECT * FROM time_zone_name LIMIT 10;

This should show some records in the database.

Now, when you next run your queries, they should correct utilise the timezone and return the required data.