How to turn on Timezone Support for MariaDB?

If you see no entries in MariaDB's time_zone table, then you have no timezone support.

select * from mysql.time_zone;
Empty set (0.001 sec)

Why turn on MariaDB Timezone Support?

That means that queries like:

> select CONVERT_TZ('2023-01-01', 'UTC', 'Europe/Berlin');
+--------------------------------------------------+
| CONVERT_TZ('2023-01-01', 'UTC', 'Europe/Berlin') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set (0.000 sec)

Will return NULL which means that queries that use CONVERT_TZ will not work. This is for example the case with some queries that Django creates, like a query like this

>>> User.objects.filter(date_joined__year=2023).only('date_joined').values_list('date_joined')
<QuerySet [(datetime.datetime(2023, 7, 22, 19, 37, 48, tzinfo=datetime.timezone.utc),)]>
>>> User.objects.filter(date_joined__month=7)
<QuerySet []> 

Filtering for a year works, but filtering for a month does not. Because under the hood, django creates a query like this:

# The query for filtering for the year 2023
SELECT * FROM auth_user WHERE `auth_user`.`date_joined` BETWEEN '2022-12-31 23:00:00' AND '2023-12-31 22:59:59.999999'

# The query for filtering for the month 7
SELECT * FROM auth_user WHERE EXTRACT(MONTH FROM CONVERT_TZ(`auth_user`.`date_joined`, 'UTC', 'Europe/Berlin')) = 7

This uses the above mentioned CONVERT_TZ function, which will return NULL if you have no timezone support.

Turn on Timezone Support in MariaDB

We need to populate the right data into the mysql.time_zone table. The docs say about this table:

The mysql.time_zone table is one of the mysql system tables that can contain time zone information. It is usually preferable for the system to handle the time zone, in which case the table will be empty (the default), but you can populate the mysql time zone tables using the mariadb-tzinfo-to-sql utility.

Run the following command to populate the table:

> mariadb-tzinfo-to-sql /usr/share/zoneinfo | mysql -u root -D mysql

The first part mariadb-tzinfo-to-sql /usr/share/zoneinfo generates a lot of SQL statements. The second part | mysql -u root -D mysql runs them all against the mysql database. The -u root part tells the command to run as root. Most probably it will ask you for the password. The -D mysql part tells the command to use the mysql database.

Now you should see a lot of entries in the table mysql.time_zone:

> select * from mysql.time_zone;

Also the django queries from above should work now, if all went well. Best of luck and 🤞.

How to put this in a Dockerfile?

In order to initialize the database with the timezone data when your docker container start and creates the DB, you can put the following line in your Dockerfile:

FROM mariadb:latest

RUN echo "USE mysql;" > /docker-entrypoint-initdb.d/timezones.sql && mariadb-tzinfo-to-sql /usr/share/zoneinfo >> /docker-entrypoint-initdb.d/timezones.sql

The first part echo "USE mysql;" > /docker-entrypoint-initdb.d/timezones.sql creates a file called timezones.sql
in the which starts with USE mysql; to use the database mysql where the timezone data should be written to.

Part two mariadb-tzinfo-to-sql /usr/share/zoneinfo >> /docker-entrypoint-initdb.d/timezones.sql appends the timezone data to this file. Since the file is located in the /docker-entrypoint-initdb.d directory, it will be executed when the "container is started for the first time" the docs say.

⚠️ This only happens when the DB is being created, not every time the container is started.

All this is described on the docker hub site under "Initializing the database contents" (sorry, there are no anchors to the headline on hub.docker.com 🤷🏽).

If you want to go more into depth this article on mariadb.org that talks about the docker file and its features, especially the latest changes (from June 2023).