Summary: in this tutorial, you will learn how to use the MySQL CONVERT_TZ()
function to convert a datetime value from one time zone to another.
Introduction to MySQL CONVERT_TZ() function
The CONVERT_TZ()
function allows you to convert a datetime value from one timezone to another.
Here’s the syntax of the CONVERT_TZ()
function:
CONVERT_TZ(datetime, from_tz, to_tz)
Code language: SQL (Structured Query Language) (sql)
In this syntax:
datetime
: The datetime value you want to convert.from_tz
: The source time zone.to_tz
: The target time zone.
The CONVERT_TZ()
function returns a datetime value converted from the source time zone to the target timezone.
The CONVER_TZ()
function returns NULL
if any argument is NULL
or invalid.
The CONVERT_TZ()
function can be useful for applications that manage date and time from different time zones, for example, international flight schedules and global event planning.
To use the CONVERT_TZ() function, you need to load data into the time zone tables.
MySQL CONVER_TZ() function examples
Let’s take some examples of using the CONVERT_TZ()
function.
1) Basic time zone conversion example
The following example uses the CONVERT_TZ()
function to convert a datetime value from UTC
to 'America/New_York'
timezone:
SELECT
CONVERT_TZ(
'2023-04-15 15:30:00', 'UTC', 'America/New_York'
) NYTime;
Code language: SQL (Structured Query Language) (sql)
Output:
+---------------------+
| NYTime |
+---------------------+
| 2023-04-15 11:30:00 |
+---------------------+
1 row in set (0.00 sec)
Code language: plaintext (plaintext)
2) Using the CONVERT_TZ() function with table data
Suppose you need to create a database that manages flight schedules. In this application, you need to display the departure and arrival times in the local time zone to the travelers.
First, create a table to store flight data:
CREATE TABLE flights (
flight_id INT AUTO_INCREMENT PRIMARY KEY,
departure_datetime DATETIME,
arrival_datetime DATETIME,
departure_time_zone VARCHAR(50) NOT NULL,
arrival_time_zone VARCHAR(50) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
Second, insert multiple schedules into the flights
table:
INSERT INTO flights (departure_datetime, arrival_datetime, departure_time_zone, arrival_time_zone)
VALUES
('2023-11-01 08:00:00', '2023-11-01 11:30:00', 'UTC', 'America/New_York'),
('2023-12-15 14:45:00', '2023-12-15 16:30:00', 'America/Los_Angeles', 'Asia/Tokyo');
Code language: SQL (Structured Query Language) (sql)
Third, retrieve the flight data with local time zone conversion:
SELECT
flight_id,
CONVERT_TZ(
departure_datetime, departure_time_zone,
arrival_time_zone
) AS local_departure_time,
CONVERT_TZ(
arrival_datetime, arrival_time_zone,
departure_time_zone
) AS local_arrival_time
FROM
flights;
Code language: SQL (Structured Query Language) (sql)
Output:
+-----------+----------------------+---------------------+
| flight_id | local_departure_time | local_arrival_time |
+-----------+----------------------+---------------------+
| 1 | 2023-11-01 04:00:00 | 2023-11-01 15:30:00 |
| 2 | 2023-12-16 07:45:00 | 2023-12-14 23:30:00 |
+-----------+----------------------+---------------------+
2 rows in set (0.00 sec)
Code language: plaintext (plaintext)
In this example, we stored flight schedules with departure and arrival datetime values, as well as the corresponding time zones.
And then we used the CONVERT_TZ()
function to convert these datetime values to the local time zones for travelers.
Summary
- Use the MySQL
CONVERT_TZ()
function to convert a datetime value from one time zone to another.