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