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
You can set the system time zone for MySQL Server at startup with the
option to mysqld_safe.
You can also set it by setting the
TZ environment variable before you
start mysqld. The permissible values for
TZ are system dependent. Consult your operating system documentation
to see what values are acceptable.
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
SUPER 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
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
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 retrieved from
TIMESTAMP columns. Values for
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
UTC_TIMESTAMP() 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
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
'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
'MET'. 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 the
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
mysql database. Use the instructions in Section
4.4.7, "mysql_upgrade — Check and Upgrade MySQL Tables".
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
/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
that corresponds to a time zone name
tz_name, invoke mysql_tzinfo_to_sql
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 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.
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".