ProblemYou 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. SolutionUse the CONVERT_TZ( ) function. DiscussionThe 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.
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 |