“Timezones are one of the complex concepts that developers have to deal with. Although tools and implementations in relational databases aim to make them tolerable, they can prove challenging and sometimes lead to errors.
In this article, however, we will discuss how you can convert a time from one timezone to another using MySQL.”
MySQL Convert_Tz() Function
The convert_tz() function in MySQL enables us to convert from one timezone to another. The function syntax is as shown:
The function takes the datetime value to be converted, the timezone from which you wish to convert, and the convert to which you want to convert.
MySQL allows you to specify the timezones as names or offset values. The function will then return the selected Datetime object in the target timezone.
Example 1
Below is an example that illustrates how to convert a time string from EST to EAT using the timezone offsets.
convert_tz('2022-08-08 22:22:22',
'+00:00',
'+03:00') as time1;
The example query above should return an output:
|-------------------|
|2022-08-09 01:22:22|
Example 2
As mentioned, we can specify the target timezone by its name. However, this does require you to download and install MySQL Timezones.
You can run the command below to load the timezones.
If you are using a timezone file, run the command:
Download the timezone files in the resource below:
Load the file:
You can then specify the target timezone with the name:
The query above should return the time converted to the target timezone as:
| time |
+---------------------+
| 2022-10-10 13:34:00 |
+---------------------+
1 row in set (0.00 sec)
End
In this short post, we discussed how to use the convert_tz function in MySQL to convert time from one timezone to another.
Happy coding!!
