MySQL - CONVERT_TZ() Function

---

---

Temporal values are supported by MySQL in the form of five datatypes: DATE, DATETIME, TIME, TIMESTAMP, YEAR. These datatypes are used to store temporal information like time at a certain point; and thus, this information keeps changing over a period. So, to handle data like this, MySQL also supports various temporal functions.

It is common knowledge that the world does not follow a single timestamp. Instead, the time is divided as time zones where each area shows a different time due to earth's rotation. This time difference is usually calculated with respect to UTC. UTC stands for Coordinated Universal Time. It is time standard and is commonly used across the world.

MySQL CONVERT_TZ() Function

MySQL provides a function called CONVERT_TZ() and it is used to convert the given date from one time zone to another time zone.

Syntax

Following is the syntax of MySQL CONVERT_TZ() function −

CONVERT_TZ(dt,from_tz,to_tz)

Parameters

This function accepts 3 parameters . The same is described below −

• date − The date value you need to convert.

• from_tz − Current time zone of the date

• to_tz − Time zone to which you need to convert the current date.

Return value

This function returns a datetime value representing the input date or datetime converted from the specified time zone (from_tz) to the another time zone (to_tz).

Example

Following example demonstrates the usage of the CONVERT_TZ() function −

SELECT CONVERT_TZ('15:30:00','+00:00','+10:00');

Output

Following output is obtained −

CONVERT_TZ('2019-11-21 15:30:00','+00:00','+10:00')
2019-11-22 01:30:00

Example

Following is another example of this function −

SELECT CONVERT_TZ('1919-06-21 21:00:00','+00:00','+10:00');

Output

Following output is obtained −

CONVERT_TZ('1919-06-21 21:00:00','+00:00','+10:00')
1919-06-21 21:00:00

Example

Following query converts GMT(Greenwich Mean Time) to IST(Indian Standard time) −

SELECT CONVERT_TZ('1886-11-20 16:29:30', '+00:00','+05:30');

Output

Following output is obtained −

CONVERT_TZ('1886-11-20 16:29:30', '+00:00','+05:30')
1886-11-20 16:29:30

Example

Following query converts IST(Indian Standard Time) to GMT(Gulf standard Time)

SELECT CONVERT_TZ('1886-11-20 16:29:30', '+05:30','+04:00');

Output

Following output is obtained −

CONVERT_TZ('1886-11-20 16:29:30', '+05:30','+04:00')
1886-11-20 16:29:30

Example

Suppose we have created a table named ORDERS using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Following query converts the date in DATE column from GMT(Greenwich Mean Time) to IST(Indian Standard time) −

SELECT OID, DATE, CONVERT_TZ(DATE, '+00:00','+05:30'), AMOUNT 
FROM ORDERS;

Output

The output is displayed as follows −

OID	DATE	CONVERT_TZ(DATE, '+00:00','+05:30')	AMOUNT
102	2009-10-08 00:00:00	2009-10-08 05:30:00.000000	3000.00
100	2009-10-08 00:00:00	2009-10-08 05:30:00.000000	1500.00
101	2009-11-20 00:00:00	2009-11-20 05:30:00.000000	1560.00
103	2008-05-20 00:00:00	2008-05-20 05:30:00.000000	2060.00

List of Timezones

Following is the list of various time zones −

S.NO	Name & Description	Relative to GMT
1	GMT Greenwich Mean Time	GMT
2	UTC Universal Coordinated Time	GMT
3	ECT European Central Time	GMT+1:00
4	EET Eastern European Time	GMT+2:00
5	ART (Arabic) Egypt Standard Time	GMT+2:00
6	EAT Eastern African Time	GMT+3:00
7	MET Middle East Time	GMT+3:30
8	NET Near East Time	GMT+4:00
9	PLT Pakistan Lahore Time	GMT+5:00
10	IST India Standard Time	GMT+5:30
11	BST Bangladesh Standard Time	GMT+6:00
12	VST Vietnam Standard Time	GMT+7:00
13	CTT China Taiwan Time	GMT+8:00
14	JST Japan Standard Time	GMT+9:00
15	ACT Australia Central Time	GMT+9:30
16	AET Australia Eastern Time	GMT+10:00
17	SST Solomon Standard Time	GMT+11:00
18	NST New Zealand Standard Time	GMT+12:00
19	MIT Midway Islands Time	GMT-11:00
20	HST Hawaii Standard Time	GMT-10:00
21	AST Alaska Standard Time	GMT-9:00
22	PST Pacific Standard Time	GMT-8:00
23	PNT Phoenix Standard Time	GMT-7:00
24	MST Mountain Standard Time	GMT-7:00
25	CST Central Standard Time	GMT-6:00
26	EST Eastern Standard Time	GMT-5:00
27	IET Indiana Eastern Standard Time	GMT-5:00
28	PRT Puerto Rico and US Virgin Islands Time	GMT-4:00
29	CNT Canada Newfoundland Time	GMT-3:30
30	AGT Argentina Standard Time	GMT-3:00
31	BET Brazil Eastern Time	GMT-3:00
32	CAT Central African Time	GMT-1:00