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.
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') AS Edmonton,
->CONVERT_TZ(@dt,'US/Central','Australia/Brisbane') AS BrisbaneG
*************************** 1. row *************************** Chicago: 2006-11-23 09:00:00 Berlin: 2006-11-23 16:00:00 London: 2006-11-23 15:00:00 Edmonton: 2006-11-23 08:00:00 Brisbane: 2006-11-24 01:00:00
Let’s hope the Brisbane participant doesn’t mind being up after midnight.
The preceding example uses time zone names, so it requires that
you have the time zone tables in the mysql
database initialized with support for
named time zones. (See the MySQL Reference Manual
for information about setting up the time zone tables.) If you can’t
use named time zones, you can specify the zones in terms of their
numeric relationship to UTC. This can be a little trickier because you
might need to account for daylight saving time. The corresponding
statement with numeric time zones looks like this:
mysql>SELECT @dt AS Chicago,
->CONVERT_TZ(@dt,'-06:00','+01:00') AS Berlin,
->CONVERT_TZ(@dt,'-06:00','+00:00') AS London,
->CONVERT_TZ(@dt,'-06:00','-07:00') AS Edmonton,
->CONVERT_TZ(@dt,'-06:00','+10:00') AS BrisbaneG
*************************** 1. row *************************** Chicago: 2006-11-23 09:00:00 Berlin: 2006-11-23 16:00:00 London: 2006-11-23 15:00:00 Edmonton: 2006-11-23 08:00:00 Brisbane: 2006-11-24 01:00:00
3.147.80.3