MySQL in a Nutshell

Name

CONVERT_TZ( )

Synopsis

CONVERT_TZ(datetime, day)

This function converts a given date and time from one given time zone to another. 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 system mysql user with a system command like chmod and restart the server. This function is available as of Version 4.1.3 of MySQL.

SELECT NOW( ) AS 'New Orleans',
       CONVERT_TZ(NOW( ), 'US/Central', 'Europe/Berlin')
          AS Berlin;
+---------------------+---------------------+
| New Orleans         | Berlin              |
+---------------------+---------------------+
| 2005-01-12 01:37:11 | 2005-01-12 08:37:11 |
+---------------------+---------------------+

This example retrieves the current time of the server located in New Orleans and converts it to the time in Berlin.