Recipe 6.12. 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.

LocationTime 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 Brisbane\G *************************** 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 Brisbane\G *************************** 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 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net