MySQL How to Convert Timezone with CONVERT_TZ Function

Use the MySQL function CONVERT_TZ(date_time, from_timezone, to_timezone) to convert a datetime value from one timezone to another.

Examples

InputCONVERT_TZ('2024-06-01 12:00:00', 'UTC', 'America/New_York')

Output2024-06-01 08:00:00

InputCONVERT_TZ('2024-12-01 15:30:00', 'Europe/London', 'Asia/Tokyo')

Output2024-12-02 00:30:00

InputCONVERT_TZ('2024-06-01 12:00:00', 'SYSTEM', 'UTC')

Output2024-06-01 16:00:00

How to Think About It

To convert timezones in MySQL, you use the CONVERT_TZ function which takes three arguments: the original datetime, the source timezone, and the target timezone. This function adjusts the datetime value according to the difference between the two timezones.

Algorithm

1

Get the original datetime value.

2

Identify the source timezone of the datetime.

3

Identify the target timezone to convert to.

4

Use the CONVERT_TZ function with these three inputs.

5

Return the converted datetime value.

Code

mysql

SELECT CONVERT_TZ('2024-06-01 12:00:00', 'UTC', 'America/New_York') AS converted_time;

Output

+---------------------+
| converted_time      |
+---------------------+
| 2024-06-01 08:00:00 |
+---------------------+

Dry Run

Let's trace converting '2024-06-01 12:00:00' from UTC to America/New_York timezone.

1

Input datetime and timezones

Original datetime: '2024-06-01 12:00:00', from_timezone: 'UTC', to_timezone: 'America/New_York'

2

Calculate timezone difference

UTC is 4 hours ahead of America/New_York during June (Daylight Saving Time)

3

Apply conversion

Subtract 4 hours from '2024-06-01 12:00:00' to get '2024-06-01 08:00:00'

Step	Datetime Value
Original	2024-06-01 12:00:00
Converted	2024-06-01 08:00:00

Why This Works

Step 1: Function Purpose

The CONVERT_TZ function changes a datetime from one timezone to another by calculating the time difference.

Step 2: Timezone Names

You must provide valid timezone names like 'UTC', 'America/New_York', or 'SYSTEM' for the function to work correctly.

Step 3: Daylight Saving Time

The function automatically adjusts for daylight saving time if the timezone supports it.

Alternative Approaches

Manual Offset Addition

mysql

SELECT DATE_ADD('2024-06-01 12:00:00', INTERVAL -4 HOUR) AS converted_time;

Simple but does not handle daylight saving time or timezone rules automatically.

Using UTC_TIMESTAMP and TIMESTAMPDIFF

mysql

SELECT DATE_ADD(UTC_TIMESTAMP(), INTERVAL -4 HOUR) AS converted_time;

Works for current time conversion but not for arbitrary datetime values.

Complexity: O(1) time, O(1) space

Time Complexity

The conversion is a direct calculation with no loops, so it runs in constant time.

Space Complexity

No extra memory is needed beyond the input and output values.

Which Approach is Fastest?

Using CONVERT_TZ is efficient and handles daylight saving automatically, unlike manual offset methods.

Approach	Time	Space	Best For
CONVERT_TZ function	O(1)	O(1)	Accurate timezone conversion with DST support
Manual Offset Addition	O(1)	O(1)	Simple fixed offset conversions without DST
UTC_TIMESTAMP with TIMESTAMPDIFF	O(1)	O(1)	Current time conversions only