In MySQL, you can convert a datetime value between one time zone to another using the CONVERT_TZ() function. This function accepts 3 arguments; the datetime value, the original time zone, and the time zone to convert to.

Syntax and examples below.

Syntax

Here’s the syntax:

CONVERT_TZ(dt,from_tz,to_tz)

Where dt is the date/time, from_tz is the original time zone, and to_tz is the time zone to convert to.

Example

Here’s an example of usage.

SELECT CONVERT_TZ('2020-12-01 01:00:00','+00:00','+08:00') AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2020-12-01 09:00:00 |
+---------------------+

Named Time Zones

You can also use named time zones, such as 'US/Eastern', 'Europe/Moscow', 'MET', etc.

To do this, you’ll need to populate the time zone tables first.

The MySQL installation procedure creates the time zone tables, but it doesn’t load them. Therefore, you’ll need to populate these tables (using the instructions in the above link) before attempting to use named time zones.

If you try to use a named time zone with this function without first populating the time zone tables, you’ll get a null value.

SELECT CONVERT_TZ('2020-12-01 01:00:00','Europe/Helsinki','US/Eastern') AS Result;

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

However, once the time zone tables have been populated, the correct result is returned:

SELECT CONVERT_TZ('2020-12-01 01:00:00','Europe/Helsinki','US/Eastern') AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2020-11-30 18:00:00 |
+---------------------+

Note that even if you do populate the time zone tables, you may need to update them periodically. This is because the information can occasionally change. For more information about this, see MySQL Server Time Zone Support on the MySQL documentation website.