MySQL in a Nutshell, 2nd Edition

Name

CONVERT_TZ()

Synopsis

CONVERT_TZ(datetime, time_zone, time_zone)

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 are not already installed on your system, go to MySQL AB’s web 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 change 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. Here is an example:

SELECT NOW() AS 'New Orleans',
CONVERT_TZ(NOW(), 'US/Central', 'Europe/Rome')
e')
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
me 
WHERE Name LIKE '%Europe%';

This will list all of the ...