MySQL CONVERT_TZ() function

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.

Was this tutorial helpful?