Rechercher dans le manuel MySQL
5.1.13 MySQL Server Time Zone Support
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.You can set the system time zone for MySQL Server at startup with the
--timezone=
option to mysqld_safe. You can also set it by setting thetimezone_name
TZ
environment variable before you start mysqld. The permissible values for--timezone
orTZ
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 fortime_zone
is'SYSTEM'
, which indicates that the server time zone is the same as the system time zone.NoteIf set to
SYSTEM
, every MySQL function call that requires a timezone calculation makes a system library call to determine the current system timezone. This call may be protected by a global mutex, resulting in contention.The initial global server time zone value can be specified explicitly at startup with the
--default-time-zone=
option on the command line, or you can use the following line in an option file:timezone
default-time-zone='timezone'
If you have the
SYSTEM_VARIABLES_ADMIN
orSUPER
privilege, you can set the global server time zone value at runtime with this statement: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 globaltime_zone
variable, but the client can change its own time zone with this statement:
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
NOW()
or
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 DATE
,
TIME
, or
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
DATE
,
TIME
, or
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:
timezone
values can be given in several
formats, none of which are case-sensitive:
The value
'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
'+10:00'
or'-6:00'
.The value can be given as a named time zone, such as
'Europe/Helsinki'
,'US/Eastern'
, or'MET'
. Named time zones can be used only if the time zone information tables in themysql
database have been created and populated.
Populating the Time Zone Tables
Several tables in the mysql
system database
exist to maintain time zone information (see
Section 5.3, “The mysql System Database”). The MySQL installation
procedure creates the time zone tables, but does not load them.
You must do so manually using the following instructions.
Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. 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 macOS. 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 nametz_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), you can use a package that is available for download at the MySQL Developer Zone:
https://dev.mysql.com/downloads/timezones.html
Download a time zone package that contains SQL statements and unpack it, then load the package file contents into the time zone tables:
shell> mysql -u root mysql < file_name
Then restart the server.
Do not use a downloadable package that
contains MyISAM
tables. MySQL uses
InnoDB
for the time zone tables. Trying to
replace them with MyISAM
tables will cause
problems.
Do not use a 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 17.4.1, “Replication Features and Issues”.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-time-zone-support.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.