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
variable. The value does not change thereafter.
You can set the system time zone for MySQL Server at startup
option to mysqld_safe. You can also set it
by setting the
TZ environment variable
before you start mysqld. The permissible
TZ are system dependent. Consult your
operating system documentation to see what values are
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
'SYSTEM', which indicates that the server
time zone is the same as the system time zone.
The initial global server time zone value can be specified
explicitly at startup with the
option on the command line, or you can use the following line
in an option file:
If you have the
privilege, you can set the global server time zone value at
runtime with this statement:
SET GLOBAL time_zone =
Per-connection time zones. Each client that connects has its
own time zone setting, given by the session
Initially, the session variable takes its value from the
but the client can change its own time zone with this
SET time_zone =
The current session time zone setting affects display and storage
of time values that are zone-sensitive. This includes the values
displayed by functions such as
CURTIME(), and values stored in and
TIMESTAMP columns are
converted from the current time zone to UTC for storage, and from
UTC to the current time zone for retrieval.
The current time zone setting does not affect values displayed by
functions such as
or values in
DATETIME columns. Nor are values in
those data types stored in UTC; the time zone applies for them
only when converting from
TIMESTAMP values. If
you want locale-specific arithmetic for
DATETIME values, convert them to
UTC, perform the arithmetic, and then convert back.
The current values of the global and client-specific time zones can be retrieved like this:
SELECT @@global.time_zone, @@session.time_zone;
timezone values can be given in several
formats, none of which are case sensitive:
'SYSTEM' indicates that the time
zone should be the same as the system time zone.
The value can be given as a string indicating an offset from
UTC, such as
The value can be given as a named time zone, such as
Named time zones can be used only if the time zone information
tables in the
mysql database have been
created and populated.
The MySQL installation procedure creates the time zone tables in
mysql database, but does not load them. You
must do so manually using the following instructions. (If you are
upgrading to MySQL 4.1.3 or later from an earlier version, you can
create the tables by upgrading your
database. Use the instructions in Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
After creating the tables, you can load them.)
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:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
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
mysql_tzinfo_to_sql like this:
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
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:
This time zone package contains
.MYI files for
MyISAM time zone tables. These tables
should be part of the
mysql database, so you
should place the files in the
subdirectory of your MySQL server's data directory. The server
should be stopped while you do this and restarted afterward.
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.
For information about time zone settings in replication setup, please see Section 16.4.1, “Replication Features and Issues”.