Skip to content
Related Articles

Related Articles

Improve Article

CONVERT_TZ() function in MySQL

  • Last Updated : 25 Nov, 2020
Geek Week

CONVERT_TZ() function in MySQL is used to convert the given DateTime from One time zone to another time zone. If the arguments are invalid, the function will return NULL. 

Syntax :

CONVERT_TZ (dt, from_tz,to_tz) 

Parameters: This method accepts a three-parameter.

  • dt: The given DateTime which we want to convert.
  • from_tz: The time zone from which we want to convert DateTime.
  • to_tz: The time zone in which we want to convert DateTime.

Returns :
It returns the DateTime after converting into a specified timezone.

Example-1 :



Converting the DateTime from GMT(Greenwich Mean Time) to IST(Indian Standard time)

SELECT CONVERT_TZ('2020-11-19 19:59:00', '+00:00', '+05:30') 
As IST_TIME;

Output :

IST_TIME
2020-11-20 01:29:00

Example-2 :

Converting the DateTime from GMT(Greenwich Mean Time) to GST (Gulf Standard Time)

SELECT CONVERT_TZ('2020-11-19 10:53:00', '+00:00', '+04:00') 
As GST_TIME;

Output :

GST_TIME
 2020-11-19 14:53:00

Example-3 :

The CONVERT_TZ function can be used to set the value of columns. To demonstrate create a table named FlightDetails.

CREATE TABLE FlightDetails(
FlightId INT NOT NULL,
Source VARCHAR(20) NOT NULL,
Destination VARCHAR(20) NOT NULL,
DepartureTime DATETIME NOT NULL,
ArrivalTime DATETIME NOT NULL,
PRIMARY KEY(FlightId )
);


Now inserting values in FlightDetails table. We will use the CONVERT_TZ function to check departure and arrival times in both source and destination airports.

INSERT INTO  
FlightDetails(FlightId, Source, Destination, 
              DepartureTime , ArrivalTime )
VALUES
(12345, 'New York', 'New Delhi', '2020-11-19 10:53:00',
                                 '2020-11-20 12:53:00');

Now, checking the FlightDetails

SELECT 
FlightId , Source ,Destination , 
DepartureTime  AS DepTimeInEST ,
CONVERT_TZ(DepartureTime, '-05:00', '+05:30') 
As DepTimeInIST ,

ArrivalTime  AS ArrTimeInIST ,
CONVERT_TZ(ArrivalTime , '+05:30', '-05:00') 
As ArrTimeInEST 
FROM FlightDetails;

Output :

FLIGHTIDSOURCE DESTINATIONDEPTIMEINEST DEPTIMEINISTARRTIMEINIST ARRTIMEINEST
12345  New YorkNew Delhi 2020-11-19 10:53:00 2020-11-19 21:23:002020-11-20 12:53:002020-11-20 02:23:00

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :