MariaDB using time zones

We are going to explain how to install and use Time zones with MariaDB on Ubuntu.

Install on Ubuntu

$ sudo apt update
$ sudo apt install tzdata

Convert to sql and import to MariaDB

Time zone data are in /usr/share/zoneinfo and is found as binaries.

$ lst /usr/share/zoneinfo

To use Time zones with MariaDB we need to convert to sql.

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ > ~/zoneinfo.sql

Now we have the zoneinfo.sql file that we can import in MariaDB database.

$ cat ~/zoneinfo.sql | sudo mysql -u root mysql -p

Configure default/global time zone in MariaDB

Default global time zone in MariaDB is OS time zone.

> SELECT @@time_zone;

We need to know the time zone within the available ones which we want to set. So we do with an sql query.

> SELECT name FROM mysql.time_zone_name;
> SELECT name FROM mysql.time_zone_name WHERE name LIKE '%Europe%';

Once we know the time zone que can set as global.

> SET GLOBAL time_zone='<time_zone>';
Query OK, 0 rows affected (0.004 sec)