Oracle 5.0, Reference Manual

Page: 838 / 3232

MySQL Server Time Zone Support
818
Use the instructions in Section 4.4.9, “
mysql_upgrade
— Check Tables for MySQL Upgrade” . After
creating the tables, you can load them.)
Note
Loading the time zone information is not necessarily a one-time operation
because the information changes occasionally. For example, the rules for
Daylight Saving Time in the United States, Mexico, and parts of Canada
changed in 2007. When such changes occur, applications that use the old rules
become out of date and you may find it necessary to reload the time zone tables
to keep the information used by your MySQL server current. See the notes at
the end of this section.
If your system has its own zoneinfo database (the set of files describing time zones), you should use
the
mysql_tzinfo_to_sql
program for filling the time zone tables. Examples of such systems
are Linux, FreeBSD, Solaris, and Mac OS X. One likely location for these files is the
/usr/share/
zoneinfo
directory. If your system does not have a zoneinfo database, you can use the downloadable
package described later in this section.
The
mysql_tzinfo_to_sql
program is used to load the time zone tables. On the command line,
pass the zoneinfo directory path name to
mysql_tzinfo_to_sql
and send the output into the
mysql
program. For example:
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
mysql_tzinfo_to_sql
reads your system's time zone files and generates SQL statements from
them.
mysql
processes those statements to load the time zone tables.
mysql_tzinfo_to_sql
also can be used to load a single time zone file or to generate leap second
information:
• To load a single time zone file
tz_file
that corresponds to a time zone name
tz_name
, invoke
mysql_tzinfo_to_sql
like this:
shell> mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql
With this approach, you must execute a separate command to load the time zone file for each named
zone that the server needs to know about.
• If your time zone needs to account for leap seconds, initialize the leap second information like this,
where
tz_file
is the name of your time zone file:
shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql
• After running
mysql_tzinfo_to_sql
, it is best to restart the server so that it does not continue to
use any previously cached time zone data.
If your system is one that has no zoneinfo database (for example, Windows or HP-UX), you can use
the package of pre-built time zone tables that is available for download at the MySQL Developer Zone:
http://dev.mysql.com/downloads/timezones.html
This time zone package contains
.frm
,
.MYD
, and
.MYI
files for the
MyISAM
time zone tables. These
tables should be part of the
mysql
database, so you should place the files in the
mysql
subdirectory
of your MySQL server's data directory. The server should be stopped while you do this and restarted
afterward.
Warning
Do not use the downloadable package if your system has a zoneinfo database.
Use the
mysql_tzinfo_to_sql
utility instead. Otherwise, you may cause a
difference in datetime handling between MySQL and other applications on your
system.