CONVERT_TZ

Convert a datetime value between time zones. This function shifts a timestamp from a source time zone to a target time zone.

Syntax

CONVERT_TZ(dt,from_tz,to_tz)

Description

CONVERT_TZ() converts a datetime value dt from the time zone 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 mysql_tzinfo_to_sql).

No conversion takes place if the value falls outside of the supported TIMESTAMP range when converted from from_tz to UTC.

Currrent

The supported range is 1970-01-01 00:00:00 to 2106-02-07 06:28:15 UTC.

< 11.5

This function returns NULL if the arguments are invalid (or named time zones have not been loaded).

See time zones for more information.

Examples

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

Using named time zones (with the time zone tables loaded):

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

The value is out of the TIMESTAMP range, so no conversion takes place:

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

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}