5.10.8. MySQL Server Time Zone Support
The MySQL server maintains several time zone settings:
The system time zone. When the server starts, it attempts to
determine the time zone of the host machine and uses it to
set the system_time_zone system variable.
The value does not change thereafter.
-
The server's current time zone. The global
time_zone system variable indicates the
time zone the server currently is operating in. The initial
value for time_zone is
'SYSTEM', which indicates that the server
time zone is the same as the system time zone. The initial
value can be specified explicitly with the
--default-time-zone=timezone
option. If you have the SUPER privilege,
you can set the global value at runtime with this statement:
mysql> SET GLOBAL time_zone = timezone;
-
Per-connection time zones. Each client that connects has its
own time zone setting, given by the session
time_zone variable. Initially, the
session variable takes its value from the global
time_zone variable, but the client can
change its own time zone with this statement:
mysql> SET time_zone = timezone;
The current values of the global and client-specific time zones
can be retrieved like this:
mysql> SELECT @@global.time_zone, @@session.time_zone;
timezone values can be given as
strings indicating an offset from UTC, such as
'+10:00' or '-6:00'. If
the time zone information tables in the mysql
database have been created and populated, you can also used
named time zones, such as 'Europe/Helsinki',
'US/Eastern', or 'MET'.
The value 'SYSTEM' can be used to indicate
that the time zone should be the same as the system time zone.
Time zone names are not case sensitive.
The MySQL installation procedure creates the time zone tables in
the mysql database, but does not load them.
You must do so manually. (If you are upgrading to MySQL 4.1.3 or
later from an earlier version, you should create the tables by
upgrading your mysql database. Use the
instructions in Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.)
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, Sun 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 pathname 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, and 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
-
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
If your system doesn't have a 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
https://dev.mysql.com/downloads/timezones.html. This 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.
Warning: Please don't 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.
For information about time zone settings in replication setup,
please see Section 6.8, “Replication Features and Known Problems”.
