CONVERT_TZ( )

This MySQL function converts a date and time for given time zone to another which is specified.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CONVERT_TZ(datetime, time_zone, time_zone)

Explanation

This function converts a given date and time from the first time zone given to the second. It requires time-zone tables to be installed in the mysql database. If they're not already installed on your system, go to MySQL AB's site (http://dev.mysql.com/downloads/timezones.html) to download the tables. Copy them into the mysql subdirectory of the data directory of MySQL. Change the ownership to the mysql system user and the user permissions with system commands such as chown and chmod, and restart the server. This function is available as of version 4.1.3 of MySQL.

Examples

SELECT NOW() AS 'New Orleans',
CONVERT_TZ(NOW(), 'US/Central', 'Europe/Rome')
AS Milan;

+---------------------+---------------------+
| New Orleans         | Milan               |
+---------------------+-------------------| 2007-03-12 20:56:15 | 2007-03-13 02:56:15 |
+---------------------+---------------------+

This example retrieves the current time of the server, which for the sake of this example is located in New Orleans, and converts this time to the time in Milan. Notice that we're using the named time zone of Europe/Rome. There's isn't a Europe/Milan choice. If a named time zone that doesn't exist is given, a NULL value is returned for that field. To find the named time zones available, check the time_zone_name table in the mysql database.

SELECT Name
FROM mysql.time_zone_name
WHERE Name LIKE '%Europe%';

This will list all of the time zone names for Europe. From here you can scan the list for one in the same zone and close to the city that you want. Incidentally, if you're converting times with this function for tables you've locked, the time_zone_name table will need to be locked, too.