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:

OrderQuantity	Price	OrderTime
100	1.58	2017-08-18 10:38:42
120	1.61	2018-03-23 07:14:16
125	1.78	2018-09-12 05:25:56
50	1.80	2019-01-16 11:52:05
200	1.72	2020-02-06 09:31:34

The statement 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:

OrderQuantity	Price	OrderTime	CONVERT_TZ_Value
100	1.58	2017-08-18 10:38:42	2017-08-18 03:38:42
120	1.61	2018-03-23 07:14:16	2018-03-23 00:14:16
125	1.78	2018-09-12 05:25:56	2018-09-11 22:25:56
50	1.80	2019-01-16 11:52:05	2019-01-16 04:52:05
200	1.72	2020-02-06 09:31:34	2020-02-06 02:31:34

❮ MySQL Functions