MySQL CONVERT_TZ() function

MySQL CONVERT_TZ() function converts a given DateTime value from one time zone to another.

The CONVERT_TZ() function in MySQL returns the DateTime value converted to the given time zone. If the value provided in the function is invalid, it returns NULL.

Syntax

MySQL CONVERT_TZ() function syntax is

CONVERT_TZ (dt, from_tz,to_tz)

Parameters

The CONVERT_TZ() function accepts three parameters.

• 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.

MySQL CONVERT_TZ() function

Let’s look at some examples of the CONVERT_TZ function in MySQL. Learning the CONVERT_TZ function with examples will help you understand the concept better.

Example 1

In this example, we are converting the DateTime from GMT(Greenwich Mean Time) to IST(Indian Standard time)

Query:

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

Output :

Example 2

In this example, we are converting the DateTime from GMT(Greenwich Mean Time) to GST (Gulf Standard Time)

Query:

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

Output :

Using CONVERT_TZ function on a Column

In this example, we will use the CONVERT_TZ function to set the value of columns.

First, let’s create a table named FlightDetails.

Query:

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:

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql