MariaDB turn on Timezone Support

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)

No Timezone Support - Why should I care?

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 🤞.