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 :
FLIGHTID | SOURCE | DESTINATION | DEPTIMEINEST | DEPTIMEINIST | ARRTIMEINIST | ARRTIMEINEST |
---|---|---|---|---|---|---|
12345 | New York | New Delhi | 2020-11-19 10:53:00 | 2020-11-19 21:23:00 | 2020-11-20 12:53:00 | 2020-11-20 02:23:00 |