Skip to main content

convert_tz

Description​

Converts a DATE or DATETIME value from one time zone to another.

This function may return different results for different time zones. For more information, see Configure a time zone.

Syntax​

DATETIME CONVERT_TZ(DATETIME|DATE dt, VARCHAR from_tz, VARCHAR to_tz)

Parameters​

  • dt: the DATE or DATETIME value to convert.

  • from_tz: the source time zone. VARCHAR is supported. The time zone can be represented in two formats: one is Time Zone Database (for example, Asia/Shanghai) and the other is UTC offset (for example, +08:00).

  • to_tz: the destination time zone. VARCHAR is supported. Its format is the same as from_tz.

Return value​

Returns a value of the DATETIME data type. If the input is a DATE value, it will be converted into a DATETIME value. This function returns NULL if any of the input parameters is invalid or NULL.

Usage notes​

For Time Zone Database, see List of tz database time zones (in Wikipedia).

Examples​

Example 1: Convert a datetime in Shanghai to Los_Angeles.

select convert_tz('2019-08-01 13:21:03', 'Asia/Shanghai', 'America/Los_Angeles');
+---------------------------------------------------------------------------+
| convert_tz('2019-08-01 13:21:03', 'Asia/Shanghai', 'America/Los_Angeles') |
+---------------------------------------------------------------------------+
| 2019-07-31 22:21:03                                                       |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)                                                       |

Example 2: Convert a date in Shanghai to Los_Angeles.

select convert_tz('2019-08-01', 'Asia/Shanghai', 'America/Los_Angeles');
+------------------------------------------------------------------+
| convert_tz('2019-08-01', 'Asia/Shanghai', 'America/Los_Angeles') |
+------------------------------------------------------------------+
| 2019-07-31 09:00:00                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

Example 3: Convert a datetime in UTC+08:00 to Los_Angeles.

select convert_tz('2019-08-01 13:21:03', '+08:00', 'America/Los_Angeles');
+--------------------------------------------------------------------+
| convert_tz('2019-08-01 13:21:03', '+08:00', 'America/Los_Angeles') |
+--------------------------------------------------------------------+
| 2019-07-31 22:21:03                                                |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Keywords​

CONVERT_TZ, timezone, time zone