MySQL Cookbook, 2nd Edition

Shifting a Date-and-Time Value to a Different Time Zone

Problem

You have a date-and-time value, but need to know what it would be in a different time zone. For example, you’re having a teleconference with people in different parts of the world and you need to tell them the meeting time in their local time zones.

Solution

Use the CONVERT_TZ() function.

Discussion

The CONVERT_TZ() function takes three arguments: a date-and-time value and two time zone indicators. The function interprets the date-and-time value as a value in the first time zone and produces a result consisting of the value shifted into the second time zone.

Suppose that I live in Chicago, Illinois in the U.S., and that I need to have a meeting with people in several other parts of the world. The following table shows the location of each meeting participant and the time zone name for each.

Location	Time zone name
Chicago, Illinois, U.S.	US/Central
Berlin, Germany	Europe/Berlin
London, United Kingdom	Europe/London
Edmonton, Alberta, Canada	America/Edmonton
Brisbane, Australia	Australia/Brisbane

If the meeting is to take place at 9 AM local time for me on November 23, 2006, what time will that be for the other participants? The following statement uses CONVERT_TZ() to calculate the local times for each time zone:

mysql>SET @dt = '2006-11-23 09:00:00';
mysql> SELECT @dt AS Chicago,
    -> CONVERT_TZ(@dt,'US/Central','Europe/Berlin') AS Berlin,
    -> CONVERT_TZ(@dt,'US/Central','Europe/London') AS London,
    -> CONVERT_TZ(@dt,'US/Central','America/Edmonton') ...