Database Reference
In-Depth Information
guments in
CONVERT_TZ()
. For instance, you will see a directory named
US
. Within it
will be a file named
Eastern
. It's from these two pieces of information that we get the
value
US/Eastern
. To install the time zone file, enter the following, changing the file path
to wherever the time zone files are located:
mysql_tzinfo_to_sql
/
usr
/
share
/
zoneinfo
|
mysql
-
p
-
u root mysql
If your server runs on Windows, you may have to go to
Oracle's site to download time
zone tables
)
. That web page will provide some instructions on installing the package you
download. After you've installed the time zone files, try the previous SQL statement again
to be sure everything was installed properly.
Rather than use the time zone where our web server happens to be located, we could use
some other time zone. We could change the time zone for the server, without having to re-
locate it or change the filesystem clock. We could set the server to a more global time
zone such as Greenwich Mean Time (GMT or UTC). Because birdwatching has some
roots in England thanks to botanists like Joseph Banks and Charles Darwin, let's use
GMT. To set the time zone, we can use the
SET
statement like so:
SET
GLOBAL time_zone
=
'GMT'
;
If we wanted to set only the time zone for the current session, we wouldn'tinclude the
GLOBAL
flag. It would be better to set this value globally in the server's configuration file
(i.e.,
my.cnf
or
my.ini
) so it isn't reset when the server is rebooted. To do that, add this line
to the
[mysqld]
section:
default
-
time
-
zone
=
'GMT'
If you use that method, instead of using
SET
, you'll have to restart the server for it to take
effect. Once you've done that, runthe
SHOW VARIABLES
statement again to see the res-
ults.
Setting the time zone on a server, knowing the user's time zone, and adjusting times using
CONVERT_TZ()
helps the user to feel he is part of the community of a website. Other-
wise, the times shown will make the user feel like he is an outsider. So learn to use
CONVERT_TZ()
so that your sites and services will be part of the globalcommunity.
