How the CONVERT_TZ() function works in Mariadb?

The CONVERT_TZ() function is a date and time function that converts a datetime value from one time zone to another time zone.

Posted on

The CONVERT_TZ() function is a date and time function that converts a datetime value from one time zone to another time zone. The function returns a datetime value that represents the same point in time as the original value, but in a different time zone. The function can handle named time zones, such as ‘Asia/Shanghai’ or ‘Europe/Paris’, as well as numeric time zones, such as ‘+08:00’ or ‘-05:00’.

Syntax

The syntax of the CONVERT_TZ() function is as follows:

CONVERT_TZ(dt, from_tz, to_tz)

The function takes three arguments, dt, from_tz, and to_tz, which are the datetime value to be converted, the time zone of the original value, and the time zone of the converted value, respectively. The arguments can be strings or expressions that evaluate to strings. The function returns NULL if any argument is NULL or invalid.

Examples

Example 1: Converting a datetime value from UTC to Hong Kong time

The following example uses the CONVERT_TZ() function to convert a datetime value from UTC to Hong Kong time. The UTC time zone is represented by ‘+00:00’ and the Hong Kong time zone is represented by ‘Asia/Hong_Kong’.

SELECT CONVERT_TZ('2023-01-01 12:00:00', '+00:00', 'Asia/Hong_Kong');

The output is:

+-----------------------------------------------------------+
| CONVERT_TZ('2023-01-01 12:00:00', '+00:00', 'Asia/Hong_Kong') |
+-----------------------------------------------------------+
| 2023-01-01 20:00:00                                       |
+-----------------------------------------------------------+

The output shows that the datetime value ‘2023-01-01 12:00:00’ in UTC is equivalent to the datetime value ‘2023-01-01 20:00:00’ in Hong Kong time.

Example 2: Converting a datetime value from New York time to Paris time

The following example uses the CONVERT_TZ() function to convert a datetime value from New York time to Paris time. The New York time zone is represented by ‘America/New_York’ and the Paris time zone is represented by ‘Europe/Paris’.

SELECT CONVERT_TZ('2023-01-01 12:00:00', 'America/New_York', 'Europe/Paris');

The output is:

+------------------------------------------------------------+
| CONVERT_TZ('2023-01-01 12:00:00', 'America/New_York', 'Europe/Paris') |
+------------------------------------------------------------+
| 2023-01-01 18:00:00                                        |
+------------------------------------------------------------+

The output shows that the datetime value ‘2023-01-01 12:00:00’ in New York time is equivalent to the datetime value ‘2023-01-01 18:00:00’ in Paris time.

Example 3: Converting a datetime value from a numeric time zone to a named time zone

The following example uses the CONVERT_TZ() function to convert a datetime value from a numeric time zone to a named time zone. The numeric time zone is represented by ‘-08:00’ and the named time zone is represented by ‘Australia/Sydney’.

SELECT CONVERT_TZ('2023-01-01 12:00:00', '-08:00', 'Australia/Sydney');

The output is:

+----------------------------------------------------------+
| CONVERT_TZ('2023-01-01 12:00:00', '-08:00', 'Australia/Sydney') |
+----------------------------------------------------------+
| 2023-01-02 06:00:00                                      |
+----------------------------------------------------------+

The output shows that the datetime value ‘2023-01-01 12:00:00’ in the numeric time zone ‘-08:00’ is equivalent to the datetime value ‘2023-01-02 06:00:00’ in the named time zone ‘Australia/Sydney’.

Example 4: Converting a datetime value with an invalid time zone

The following example uses the CONVERT_TZ() function to convert a datetime value with an invalid time zone. The invalid time zone is represented by ‘Foo/Bar’.

SELECT CONVERT_TZ('2023-01-01 12:00:00', '+00:00', 'Foo/Bar');

The output is:

+-------------------------------------------------+
| CONVERT_TZ('2023-01-01 12:00:00', '+00:00', 'Foo/Bar') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+

The output shows that the function returns NULL, as expected. The function returns NULL if the time zone argument is not a valid name or a valid numeric value.

Example 5: Converting a datetime value with a NULL argument

The following example uses the CONVERT_TZ() function to convert a datetime value with a NULL argument.

SELECT CONVERT_TZ('2023-01-01 12:00:00', NULL, 'Asia/Hong_Kong');

The output is:

+----------------------------------------------------+
| CONVERT_TZ('2023-01-01 12:00:00', NULL, 'Asia/Hong_Kong') |
+----------------------------------------------------+
| NULL                                               |
+----------------------------------------------------+

The output shows that the function returns NULL, as expected. The function returns NULL if any argument is NULL.

There are some other functions that are related to the CONVERT_TZ() function in Mariadb. They are:

  • UTC_TIMESTAMP(): This function returns the current UTC date and time as a datetime value.
  • NOW(): This function returns the current date and time as a datetime value in the session time zone.
  • TIMESTAMP(): This function converts one or two arguments to a datetime value in the session time zone.
  • DATE_FORMAT(): This function formats a date or a datetime value according to a specified format string.

Conclusion

The CONVERT_TZ() function is a useful function to convert a datetime value from one time zone to another time zone. It can handle named time zones and numeric time zones. It returns a datetime value that represents the same point in time as the original value, but in a different time zone. It is similar to the TIMESTAMP() function, but with more flexibility. It is also related to some other functions that provide date and time information or formatting.