Working with MySQL-MariaDB Time Zones

For any program, whether it’s a web app, API server, desktop software, tablet or phone app, working with the time zones is a pretty common task.

In this article, I am going to show you how to install time zone databases to MySQL and MariaDB database server and use it. I have tested the steps shown in this article on CentOS 8 and Ubuntu 18.04 LTS. But it should work on CentOS/RHEL 7+, Ubuntu 18.04+ and Debian 10+. So, let’s get started.

Prerequisites:

You must have MySQL or MariaDB installed on your Linux OS (i.e. CentOS/RHEL, Ubuntu/Debian). If you need any assistance on installing MySQL/MariaDB, there are many articles on LinuxHint.com which you can check.

Installing Time Zone Data on CentOS/RHEL:

In CentOS/RHEL, the tzdata package provides time zone information. The tzdata package should be installed by default.

If in any case, it is not installed, you can install it with the following commands:

$ sudo dnf makecache
$ sudo dnf install tzdata

NOTE: On CentOS/RHEL 7, use yum instead of dnf.

Installing Time Zone Data on Ubuntu/Debian:

In Ubuntu/Debian, the tzdata package provides time zone information. The tzdata package should be installed by default.

If in any case, it is not installed, you can install it with the following commands:

$ sudo apt update
$ sudo apt install tzdata

Converting Time Zone Data to SQL:

The time zone data should be in the /usr/share/zoneinfo/ directory of CentOS/RHEL, and Ubuntu/Debian OS.

$ ls /usr/share/zoneinfo/

As you can see, the time zone data is nicely arranged in different folders.

The time zone data files are binary. You can’t use them directly with MySQL/MariaDB databases.

$ cat /usr/share/zoneinfo/America/Toronto

You must convert the binary time zone data (from the /usr/share/zoneinfo/ directory) to SQL using the mysql_tzinfo_to_sql program.

To convert the time zone data to SQL, run mysql_tzinfo_to_sql as follows:

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

A new file zoneinfo.sql should be created in your HOME directory. You can import the time zone information to your MySQL/MariaDB database from this file.

Importing Time Zone Data to MySQL/MariaDB:

You can import the time zone information from the zoneinfo.sql file into the mysql database as follows:

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

Now, type in your MySQL/MariaDB database root password and press <Enter>. The time zone information should be imported.

Setting Default/Global Time Zone in MySQL/MariaDB:

By default, the default/global time zone of MySQL/MariaDB is set to the OS time zone. You can set a different default/global time zone if you want.

> SELECT @@time_zone;

First, you have to find the time zone name which you want to set. You can find all the available time zone names with the following SQL statement:

> SELECT name FROM mysql.time_zone_name;

You can also search for your desired time zone name as follows:

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

Now, you can set your desired time zone as default/global time zone as follows:

> SET GLOBAL time_zone='<time_zone>';

Your desired time zone should be set as default/global time zone.

> SELECT @@time_zone;

Working with Time Zones in MySQL/MariaDB:

The CONVERT_TZ() function is used to convert the time zone of a datetime in MySQL/MariaDB.

The syntax of the CONVERT_TZ() function is:

CONVERT_TZ(datetime, from_tz, to_tz)

Here, from_tz and to_tz can be a time zone name (i.e. Asia/Dhaka, America/New_York), or time zone offset (i.e. +06:00, -02:00).

The datetime is converted from from_tz to to_tz time zone.

You can print the current timestamp (current date and time) of your computer with the following SQL statement:

> SELECT CURRENT_TIMESTAMP();

Now, let’s say, you want to convert the time zone of the current date and time of your computer to  Europe/London. To do that, you can run the CONVERT_TZ() function as follows:

> SELECT CURRENT_TIMESTAMP() AS DT_Dhaka, CONVERT_TZ(CURRENT_TIMESTAMP(),
 @@time_zone, 'Europe/London') AS DT_London;

As you can see, the time zone of the current datetime is converted to Europe/London successfully.

You can also convert time zone of specific datetime as follows:

> SET @dt='2001-01-03 11:02:11';
> SELECT @dt, CONVERT_TZ(@dt, 'America/Panama', 'Europe/London');

You can also convert the time zone of the datetime fields of a table. For demonstration, I will use a simple birthday table in this article.

First, create a birthday table as follows:

> CREATE TABLE birthday( name VARCHAR(20) NOT NULL,
birth_timestamp DATETIME NOT NULL );

The birthday table has only 2 fields, name and birth_timestamp as you can see in the screenshot below.

> DESCRIBE birthday;

Now, insert some dummy birthday data into the birthday table as follows:

> INSERT INTO birthday VALUES('Bob', '1997-10-11 12:11:11'),
('Alex', '1987-01-11 01:41:01'),('Lily', '2001-01-02 20:11:36');

Here are the dummy birthday data.

> SELECT * FROM birthday;

Now, you can convert the time zone of all the birthday timestamps to Europe/London as follows:

> SELECT name, birth_timestamp, CONVERT_TZ(birth_timestamp, @@time_zone,
 'Europe/London') AS london_birth_timestamp FROM birthday;

As you can see, the birthday time zones are converted correctly.

So, that’s basically how you work with MySQL/MariaDB time zones. Thanks for reading this article.

from Linux Hint https://ift.tt/2WO3QBv