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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.