MySQL CONVERT_TZ() function
Last Updated : 15 Jun, 2024
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 :
| IST_TIME |
| 2020-11-20 01:29:00 |
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 :
| GST_TIME |
| 2020-11-19 14:53:00 |
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:
| 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 |
Similar Reads
MySQL | CONVERT( ) Function
The MySQL CONVERT() function is used for converting a value from one datatype to a different datatype. The MySQL CONVERT() function is also used for converting a value from one character set to another character set. It accepts two parameters which are the input value and the type to be converted in
2 min read
MySQL | CONV( ) Function
The MySQL CONV() function is used for converting a number from one numeric base system to another. The value returned by the CONV() function is in the form of a string value. It accepts three parameters which are the value to be converted, the current numeric base system and the numeric base system
2 min read
PLSQL | CONVERT Function
The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. The CONVERT function in PLSQL is used to convert a string from one character set to another. Generally, the dest
2 min read
COT() Function in MySQL
COT() function : This function in MySQL is used to return the cotangent of a specified number. If the specified number is 0, an error or NULL will be returned. In a right triangle, the cotangent of an angle is the length of it's adjacent side divided by the length of the opposite side. Similarly, th
1 min read
MySQL | CAST( ) Function
The MySQL CAST() function is used for converting a value from one datatype to another specific datatype. The CAST() function accepts two parameters which are the value to be converted and the datatype to which the value needs to be converted. The datatypes in which a given value can be converted are
3 min read
MySQL | COMPRESS( ) Function
The MySQL COMPRESS() function is used for the compression of a string. The value returned by the COMPRESS() function is a binary string. The COMPRESS() function stores non-empty strings as a four-byte length of the uncompressed string, which is then followed by the compressed string. If the string e
1 min read
Conversion Function in SQL
In SQL data type conversion is important for effective database management and accurate query results. Data type conversion ensures that data from different sources or columns can be correctly interpreted and manipulated, especially when dealing with different formats like numbers, text, dates, and
5 min read
EXPORT_SET() function in MySQL
EXPORT_SET() : This function helps to return a string which will show the bits in number. The function requires 5 arguments for its functioning. The function converts first argument i.e integer to binary digits, then returns “on†if binary digit is 1 and “off†if binary digit is 0. Syntax : EXPORT_S
2 min read
MySQL | BINARY Function
The MySQL BINARY function is used for converting a value to a binary string. The BINARY function can also be implemented using CAST function as CAST(value AS BINARY). The BINARY function accepts one parameter which is the value to be converted and returns a binary string. Syntax: BINARY value Parame
1 min read
MySQL BIN() Function
The BIN() function in MySQL converts a decimal number to its binary equivalent. The BIN() function is equivalent to the CONV() function written in the format CONV(number,10,2). In this format, the CONV() function converts the number 'number' from base 10 (decimal) to base 2 (binary). It is important
1 min read