MySQL Converts From One Time Zone to Another

“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',
    '+03:00') as time1;


The example query above should return an output:

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

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

If you are using a timezone file, run the command:

mysql_tzinfo_to_sql tz_file tz_name | mysql -u root -p mysql

Download the timezone files in the resource below:

Load the file:

mysql -u root -p mysql < file_name

You can then specify the target timezone with the name:

mysql> SELECT CONVERT_TZ('2022-10-10 14:34:00','US/Eastern','US/Central') AS time;

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)



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!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list