SQL Tutorial SQL Advanced SQL Database SQL References

MySQL CONVERT_TZ() Function



The MySQL CONVERT_TZ() function is used to convert a date or datetime value from one time zone to another time zone.

Syntax

CONVERT_TZ(datetime, from_tz, to_tz)

Parameters

datetime Required. Specify the date or datetime value to convert.
from_tz Required. Specify the time zone to convert from.
to_tz Required. Specify the time zone to convert to.

Return Value

Returns the converted datetime value.

Example 1:

The example below shows the usage of CONVERT_TZ() function.

mysql> SELECT CONVERT_TZ('2018-08-18', 'GMT', 'MET');
Result: '2018-08-18 02:00:00'

mysql> SELECT CONVERT_TZ('2018-08-18 10:38:42', 'GMT', 'MET');
Result: '2018-08-18 12:38:42'

mysql> SELECT CONVERT_TZ('2018-08-18', '+00:00', '+10:00');
Result: '2018-08-18 10:00:00'

mysql> SELECT CONVERT_TZ('2018-08-18 10:38:42', '+00:00', '+10:00');
Result: '2018-08-18 20:38:42'

Example 2:

Consider a database table called Orders with the following records:

OrderQuantityPriceOrderTime
1001.582017-08-18 10:38:42
1201.612018-03-23 07:14:16
1251.782018-09-12 05:25:56
501.802019-01-16 11:52:05
2001.722020-02-06 09:31:34

The query given below can be used to convert the records of OrderTime column from GMT to MST:

SELECT *, 
CONVERT_TZ(OrderTime, 'GMT', 'MST') AS CONVERT_TZ_Value 
FROM Orders;

This will produce the result as shown below:

OrderQuantityPriceOrderTimeCONVERT_TZ_Value
1001.582017-08-18 10:38:422017-08-18 03:38:42
1201.612018-03-23 07:14:162018-03-23 00:14:16
1251.782018-09-12 05:25:562018-09-11 22:25:56
501.802019-01-16 11:52:052019-01-16 04:52:05
2001.722020-02-06 09:31:342020-02-06 02:31:34

❮ MySQL Functions

5