MySQL 4.1.3 et les fuseaux horaires

Introduction

MySQL 4.1.3 inclut un support amélioré pour la manipulation des valeurs temporelles en combinaison avec les fuseaux horaires. Cette documentation présente l’installation et les fonctionnalités offertes.

Généralités

À partir de la version MySQL 4.1.3, les fonctions current_date( ), current_time( ), current_timestamp( ), from_unixtime( ), localtime, localtimestamp, now, sysdate et unix_timestamp( ) retourne les valeurs en fonction du fuseau horaire courant de la connexion, alors que les fonctions utc_date( ), utc_time( ) et utc_timestamp( ) retournent les valeurs au format UTC (Universal Coordinated Time, date et heure correspondant au fuseau horaire de Greenwich).

Par ailleurs, la nouvelle fonction convert_tz fournit la possibilité de convertir une valeur au format datetime ou timestamp d’un fuseau horaire vers un autre fuseau horaire (cf paragraphe 4).

Dans les versions précédentes de MySQL, le fuseau horaire était appliqué au serveur MySQL avec l’option --timezone=timezone_name dans le lancement de mysqld_safe ou bien en appliquant la variable d’environnement TZ lors du démarrage de mysqld. Tout ceci est modifié avec la version 4.1.3 de MySQL.

Avec la version 4.1.3, MySQL maintient trois niveaux de fuseaux horaires :

• le fuseau horaire système ;
• le fuseau horaire courant du serveur MySQL ;
• le fuseau horaire pour chaque client

Le fuseau horaire système

Lorsque le serveur MySQL démarre, le fuseau horaire de la machine est utilisé pour appliquer automatiquement la variable system_time_zone.

mysql> show variables like 'syst%';

+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Paris, Madrid |
+------------------+---------------+

La variable system_time_zone remplace l’ancienne variable système timezone. Aussi les utilisateurs effectuant une migration vers la version 4.1.3 doivent s’assurer de migrer également l’utilisation de la variable système timezone vers la variable système system_time_zone dans les lancements mysqld_safe, mysqld, etc.

--timezone=timezone_name --system_time_zone=timezone_name

Le fuseau horaire du serveur MySQL

La variable globale dynamique time_zone spécifie le fuseau horaire courant du serveur. Sa valeur initiale est SYSTEM, ce qui signifie simplement que le fuseau horaire du serveur MySQL est le même que le fuseau horaire système.

mysql> show variables like 'time_zone%';
      
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+

La valeur initiale du fuseau horaire du serveur MySQL peut être explicitement modifiée avec l’option --default_time_zone=timezone. Les utilisateurs disposant du privilège SUPER peuvent également modifier cette valeur globale au niveau du serveur avec la commande SET GLOBAL time_zone :

mysql> SET GLOBAL time_zone = timezone;

Exemples :

mysql> SET GLOBAL time_zone = '+6:00';
mysql> SET GLOBAL time_zone = 'MET';

Les valeurs time_zone sont données avec des chaînes de caractères pour lesquelles plusieurs format sont possibles :

• soit avec une valeur relative à l’UTC (Universal Coordinated Time, date et heure de Greenwich) sous la forme [+ | - ]HH:MM.
• soit avec le nom complet du fuseau horaire (exemple : 'Mountain Daylight Time','Central European Time')
• soit avec un identifiant ou abbréviation qui représente le fuseau horaire (exemple : MDT, GMT, CET, etc.).

Ci-dessous les valeurs possibles pour Paris lors de l’affectation de la variable global time_zone : UTC : '+01:00'. Identifiant explicite : MET (Middle European Time) ou CET (Central Europe Time).

mysql> set GLOBAL time_zone='+2:00';

ERROR 1105 (HY000): Binary logging and replication
changing of the global server time zone

Pour récupérer la valeur courante de la variable globale time_zone :

mysql> select @@global.time_zone;

Le fuseau horaire du client

Chaque client qui se connecte à un serveur MySQL a désormais son propre fuseau horaire, spécifié par la variable dynamique de session time_zone. La valeur initiale de cette variable est la même que la variable globale time_zone. Au niveau de la session, le fuseau horaire du client peut être dynamiquement modifié :

mysql> SET time_zone = timezone;

Exemple :

mysql> SET time_zone = '+2:00';

Pour afficher la valeur courante time_zone de la session :

mysql> select @@session.time_zone;

+---------------------+
| @@session.time_zone |
+---------------------+
| +00:00              |
+---------------------+

Installation des tables de référence timezone_%

L’utilisation des abbréviations ou noms complets des fuseaux horaires avec MySQL 4.1.3 ne peut être effective que sur les systèmes Unix Like pour l’heure actuelle et à la condition que les tables systèmes relatives aux fuseaux horaires dans la base mysql soient créées et chargées.

Ces tables systèmes sont au nombre de 5 :

• time_zone
• time_zone_leap_second
• time_zone_name
• time_zone_transition
• time_zone_transition_type

Bien qu’à partir de la version 4.1.3 de MySQL les tables systèmes time_zone% soient automatiquement créées dans la base mysql, ces tables systèmes ne sont pas automatiquement chargées. Cette dernière étape doit être réalisée manuellement.

Étape 1 : uniquement dans le cas d’une migration à partir d’une version 4.1.2 ou inférieure

Dans ce cas de figure, les tables systèmes time_zone% doivent être créées en exécutant le script mysql_fix_privilege_tables.sql installé dans le répertoire $MYSQL_INSTALL_DIR/scripts/mysql_fix_privilege_tables.sql :

% cd $MYSQL_INSTALL_DIR/scripts
% mysql -uroot -P<port Number> -S<socket file Path>

use mysql;
source mysql_fix_privilege_tables.sql
exit;

Étape 2 : pour tous les serveurs MySQL version 4.1.3 ou supérieure en environnement Unix Like

La seconde étape consiste à charger les tables systèmes time_zone% et le programme mysql_tzinfo_to_sql fourni avec la distribution MySQL est prévu à cet effet. Le programme mysql_tzinfo_to_sql lit les fichiers des zones horaires du système d’exploitation et génère les ordres SQL en conséquence pour renseigner les tables système time_zone%, ordres SQL qui sont ensuite exécutés par mysql.

Pour exécuter le programme mysql_tzinfo_to_sql avec succès, il est indispensable de savoir où les fichiers des zones horaires sont localisés sur le système d’exploitation. sous RedHat et Mandrake : /usr/share/zoneinfo , sous Solaris 2.8 : /usr/share/lib/zoneinfo. Le répertoire où sont installés les fichiers timezone doit être donné au programme mysql_tzinfo_to_sql.

% mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | mysql -u root mysql

La fonction convert_tz

La nouvelle fonction convert_tz permet de convertir une valeur datetime entre deux fuseaux horaires.

mysql > select CONVERT_TZ(temporal_expression, from_timezone, to_timezone); 

La fonction convert_tz prend trois arguments. Le premier argument est une valeur datetime ou timestamp, alors que les deux derniers arguments indiquent un fuseau horaire donné par le format '[+|-]HH:MM' ou par l’abbréviation / nom complet du fuseau horaire (comme 'GMT' ou 'Greenwich Mean Time').

Rappel : Les fuseaux horaires nommés ne peuvent être utilisés que si les tables systèmes timezone_% sont correctement chargées.

Le grand intérêt de la fonction convert_tz permet l’utilisation des abbréviations ou noms complets des fuseaux horaires grâce aux définitions chargées dans les tables systèmes timezone_%, mais prudence car tout dépend de l’OS pour les noms et abbréviations pour rappel.

La fonction convert_tz suppose que le premier argument soit une valeur datetime dans le fuseau horaire donné par l’argument from_timezone. La fonction convertit alors cette valeur datetime en la valeur datetime correspondante dans le fuseau horaire donné par l’argument to_timezone.

La fonction convert_tz retourne la valeur NULL si les arguments sont invalides et en particulier si l’un des identifiants ou noms des fuseaux horaires n’a pu être résolu. Exemple : ci-dessous le tableau des fuseaux horaires de Londres, Paris et Mexico

Ainsi, sachant qu’il est 9h00 à Paris le 01/10/2004. Pour déterminer automatiquement la date et l’heure correspondantes à Mexico et à Londres : pour Londres

mysql > select CONVERT_TZ('2004-10-01 09:00:00', '+01:00', '+00:00'); 
mysql > select CONVERT_TZ('2004-10-01 09:00:00', 'CET', 'GMT'); 

+-----------------------------------------------------+
| convert_tz('2004-10-01 09:00:00','+01:00','+00:00') |
+-----------------------------------------------------+
| 2004-10-01 08:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.02 sec)

et pour Mexico :

mysql > select CONVERT_TZ('2004-10-01 09:00:00', '+01:00', '-06:00'); 
mysql > select CONVERT_TZ('2004-10-01 09:00:00', 'CET', 'CST'); 

+-----------------------------------------------------+
| convert_tz('2004-10-01 09:00:00','+01:00','-06:00') |
+-----------------------------------------------------+
| 2004-10-01 02:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.02 sec)