(mysql.info.gz) Time zone support
Info Catalog
(mysql.info.gz) Problems with character sets
(mysql.info.gz) Localisation
5.8.8 MySQL Server Time Zone Support
------------------------------------
Before MySQL 4.1.3, you can set the time zone for the server with the
`--timezone=TIMEZONE_NAME' option to `mysqld_safe'. You can also set
it by setting the `TZ' environment variable before you start `mysqld'.
The allowable values for `--timezone' or `TZ' are system-dependent.
Consult your operating system documentation to see what values are
acceptable.
Beginning with MySQL 4.1.3, the 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 server's current time zone. The global `time_zone' system
variable indicates the time zone the server currently is operating
in. The initial value 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 this is the same as the global `time_zone' variable, but
can be reset with this statement:
mysql> SET time_zone = TIMEZONE;
The current values of the global and per-connection 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-related 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'' indicates 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 Upgrading-grant-tables.)
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
`http://dev.mysql.com/downloads/timezones.html'. This package contains
`.frm', `.MYD', and `.MYI' files for the `MyISAM' time zone tables.
These tables should belong to the `mysql' database, so you should place
the files in the `mysql' subdirectory of your MySQL server's data
directory. The server should be shut down 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
look into Replication Features.
Info Catalog
(mysql.info.gz) Problems with character sets
(mysql.info.gz) Localisation
automatically generated byinfo2html