In MariaDB, CONVERT_TZ() is a built-in date and time function that converts a datetime value from one time zone to another.

When you call the function, you pass three arguments: the time, the time zone to convert from, and the time zone to convert to.

Syntax

The syntax goes like this:

CONVERT_TZ(dt,from_tz,to_tz)

Where dt is the datetime expression, from_tz is the time zone to convert from, and to_tz is the time zone to convert to.

Example

Here’s an example:

SELECT CONVERT_TZ('2021-05-10 01:00:00', '+00:00', '+10:00');

Result:

+-------------------------------------------------------+
| CONVERT_TZ('2021-05-10 01:00:00', '+00:00', '+10:00') |
+-------------------------------------------------------+
| 2021-05-10 11:00:00                                   |
+-------------------------------------------------------+

Here, the original time zone is +00:00, and we converted it to +10:00.

Here’s what happens if we use a different starting time zone:

SELECT CONVERT_TZ('2021-05-10 01:00:00', '+03:00', '+10:00');

Result:

+-------------------------------------------------------+
| CONVERT_TZ('2021-05-10 01:00:00', '+03:00', '+10:00') |
+-------------------------------------------------------+
| 2021-05-10 08:00:00                                   |
+-------------------------------------------------------+

Named Time Zones

Named time zones can be used, but this requires that the various time zone tables have been loaded.

Here’s what happens when the time zone tables aren’t populated:

SELECT 
CONVERT_TZ('2021-05-10 01:00:00', 'GMT', 'Pacific/Chatham');

Result:

+-------------------------------------------------------------+
| CONVERT_TZ('2021-05-10 01:00:00', 'GMT', 'Pacific/Chatham') |
+-------------------------------------------------------------+
| NULL                                                        |
+-------------------------------------------------------------+

The result is null, because there’s no time zone data in the time zone tables.

Here’s the same query again, but this time with data in the time zone tables:

SELECT 
CONVERT_TZ('2021-05-10 01:00:00', 'GMT', 'Pacific/Chatham');

Result:

+-------------------------------------------------------------+
| CONVERT_TZ('2021-05-10 01:00:00', 'GMT', 'Pacific/Chatham') |
+-------------------------------------------------------------+
| 2021-05-10 13:45:00                                         |
+-------------------------------------------------------------+

Out of Range Datetime Values

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.

Example:

SELECT CONVERT_TZ('2040-05-10 01:00:00', '+00:00', '+10:00');

Result:

+-------------------------------------------------------+
| CONVERT_TZ('2040-05-10 01:00:00', '+00:00', '+10:00') |
+-------------------------------------------------------+
| 2040-05-10 01:00:00                                   |
+-------------------------------------------------------+

Invalid Arguments

If any of the arguments are invalid, CONVERT_TZ() returns null.

Example:

SELECT CONVERT_TZ('2040-05-10 01:00:00', '+00:00', '+90:00');

Result:

+-------------------------------------------------------+
| CONVERT_TZ('2040-05-10 01:00:00', '+00:00', '+90:00') |
+-------------------------------------------------------+
| NULL                                                  |
+-------------------------------------------------------+

In this case, I tried to convert the datetime expression to an invalid time zone (+90:00), and so null was returned.

Null Arguments

If any argument is null, the result is null:

SELECT 
    CONVERT_TZ(null, '+00:00', '+90:00') AS "1",
    CONVERT_TZ('2021-05-10 01:00:00', null, '+90:00') AS "2",
    CONVERT_TZ('2021-05-10 01:00:00', '+00:00', null) AS "3";

Result:

+------+------+------+
| 1    | 2    | 3    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Missing Argument

Calling CONVERT_TZ() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT CONVERT_TZ();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONVERT_TZ'