Je vais vous parler d’une réflexion que j’ai en ce moment, causée par un développement sur un produit qui se veut à destination d’utilisateurs répartis autour du globe.

Jusqu’ici, je n’avais jamais vraiment été confronté à de véritables problèmes de gestion des dates dans mes applications. Je stockais les dates et heures en base de données dans des champs de type DATETIME (avec MySQL). Les dates enregistrées l’étaient en partant du principe qu’elles étaient valables en France, et donc au moment de lire les dates pour les afficher cela ne posait pas de soucis pour des utilisateurs situés en France là encore.

Notez bien que j’utilise MySQL, qui a la très mauvaise idée de stocker les dates sans la moindre information de timezone. Donc quand vous enregistrez la valeur ‘2011-04-12 11:04:12’, il l’enregistre telle quelle, sans plus d’information. À vous de vous débrouiller avec ça. Encore une fois, tant que vous êtes dans le même pays pour la lecture et l’écriture, cela ne pose aucun problème. Ça devient plus délicat quand ce n’est pas le cas.
Je vais m’expliquer en détail, mais sachez qu’avec d’autres moteurs de base de données, comme PostgreSQL par exemple (je ne connais pas les autres ; j’ai travaillé un peu avec Oracle il y a 10 ans, mais je n’ai pas de souvenir à ce niveau), il y a des champs permettant de gérer les timezones, ce qui évite bien des soucis la plupart du temps.

Le cas d’utilisation problématique apparaît lorsque vous devez lire une date et l’afficher pour un utilisateur situé dans un fuseau horaire différent de celui qui a été à l’origine de l’enregistrement.
Ce qui est normalement prévu, c’est de convertir la date depuis sa timezone de départ vers la timezone d’arrivée. Si on part du principe que la timezone utilisée par le serveur n’a pas été modifiée, on peut se baser dessus pour faire la conversion. Pour cela, on peut utiliser la fonction CONVERT_TZ(). Par exemple, pour lire une date enregistrée en France et l’afficher pour un utilisateur québécois, on peut écrire ceci :

SELECT CONVERT_TZ(date, @@session.time_zone, '-5:00') FROM MaTable;

Le résultat est que la date stockée dans le champ date est convertie, depuis la timezone définie par le système (ou éventuellement dans la configuration de MySQL), vers une date dont la timezone a 5 heures de retard sur le fuseau zéro.
Donc si la date en base est ‘2011-04-12 11:04:12’, que le système a une timezone par défaut configurée pour être celle de la France, qu’on est actuellement en hiver (donc la timezone serveur est ‘+01:00’), le résultat sera ‘2011-04-12 05:04:12’.

C’est parfait !

Oui mais non. En Europe, le passage à l’heure d’été se fait le dernier dimanche de mars, alors qu’en Amérique du Nord il se fait le deuxième dimanche de mars ; les passages à l’heure d’hiver se faisant respectivement le premier dimanche de novembre et le dernier dimanche d’octobre. Il y a donc 3 semaines dans l’année pendant lesquelles il n’y a plus que 5 heures de décalage horaire entre la France et le Québec, au lieu des 6 heures habituelles.

Imaginons que je convertisse la date ‘2010-03-21 15:27:10’, par la même manière que précédemment, que se passe-t-il ?

CONVERT_TZ('2010-03-21 15:27:10', @@session.time_zone, '-05:00')

Il faut comprendre que le premier paramètre est indépendant des deux suivants. Le deuxième paramètre prend la valeur de la timezone locale, définie par le système ; ce qui me donne ‘+01:00’ (décalage en France en hiver) ou ‘+02:00’ (décalage en France en été). Mais pour le troisième paramètre, j’ai donné une valeur fixe, qui va s’ajouter au paramètre précédent.
Donc, suivant que je fasse cette conversion en été ou en hiver (et donc suivant la valeur du second paramètre), le décalage horaire qui va être calculé sera de 6 ou de 7 heures.

Ce n’est pas bon du tout ! Il n’y a jamais 7 heures de décalage horaire entre la France et le Québec !

Il faudrait donc que je sache moduler le troisième paramètre, pour lui donner une valeur différente en fonction de la date en cours (‘-05:00’ en hiver et ‘-04:00’ en été). Ainsi, le décalage sera toujours bien de 6 heures…
Sauf que la date en question (21 mars) tombe pile sur l’un des créneaux pendant lesquels il n’y a plus que 5 heures de décalage horaire entre la France et le Québec ! Comment faire pour interpréter cela correctement, et obtenir la bonne valeur (‘2010-03-21 10:27:10’, pour info) ?

Après avoir pas mal cherché, il semblerait qu’il ne faille pas faire confiance à MySQL. Du tout. Il ne sait pas gérer les timezones et encore moins les passages heure d’été/heure d’hiver ; donc quand il enregistre une date, il la stocke telle quelle et il vous laissera vous débrouiller quand vous voudrez l’afficher.
Pour ne pas faire confiance à MySQL, le plus simple est de stocker toutes les dates telles qu’elles sont sur le temps UTC, donc sans décalage par rapport au temps universel. Au moment de la lecture, les dates doivent alors être récupérées de la même manière (temps UTC), puis converties au moment de l’affichage, en fonction des préférences locales de l’utilisateur.

Pour être plus clair, il suffit de faire la conversion au moment de l’écriture :

INSERT INTO MaTable SET date = CONVERT_TZ('2017-05-06 05:49:00', @@session.time_zone, '+00:00');

Au moment de la lecture, plus besoin de faire de conversion quand on lit les données en base. Par contre, il faudra convertir au moment de l’affichage, comme je l’ai dit plus haut.

Mais cette requête utilise la timezone locale du serveur, pour calculer le décalage horaire (ici ‘+01:00’ ou ‘+02:00’ si le serveur est en France, suivant qu’on soit en été ou en hiver au moment où la requête est exécutée). Si c’est un utilisateur qui fournit une date et heure, il va falloir récupérer sa timezone locale (‘-05:00’ ou ‘-04:00’ s’il est au Québec, suivant la période de l’année).

Par contre, si les seules dates que vous enregistrez sont les date et heure courantes, en utilisant la fonction NOW(), il peut être plus simple de configurer le serveur pour qu’il reste sur l’UTC.
Cela peut être fait en modifiant le fichier de configuration (‘/etc/mysql/my.cnf) :

default_time_zone = '+00:00'

Ou en modifiant le paramètre de la connexion en cours :

SET @@session.time_zone = '+00:00';

Quelques liens sur le sujet :

• http://stackoverflow.com/questions/13916747/set-mysql-database-timezone-to-gmt
• http://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra/1650910#1650910
• http://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc