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

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition