Convert a datetime value between time zones. This function shifts a timestamp from a source time zone to a target time zone.
CONVERT_TZ(dt,from_tz,to_tz)CONVERT_TZ() converts a datetime value dt from the given by from_tz to the time zone given by to_tz and returns the resulting value.
In order to use named time zones, such as GMT, MET or Africa/Johannesburg, the time_zone tables must be loaded (see ).
No conversion will take place if the value falls outside of the supported TIMESTAMP range ('1970-01-01 00:00:01' to '2038-01-19 05:14:07' UTC) when converted from from_tz to UTC.
This function returns NULL if the arguments are invalid (or named time zones have not been loaded).
See for more information.
Using named time zones (with the time zone tables loaded):
The value is out of the TIMESTAMP range, so no conversion takes place:
This page is licensed: GPLv2, originally from
SELECT CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2016-01-01 22:00:00 |
+-----------------------------------------------------+SELECT CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg');
+---------------------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg') |
+---------------------------------------------------------------+
| 2016-01-01 14:00:00 |
+---------------------------------------------------------------+SELECT CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 1969-12-31 22:00:00 |
+-----------------------------------------------------+