Quel format utiliser pour stocker des dates en base de données avec MySQL/MariaDB?

Cette question peut paraitre farfelue au premier abord, mais selon les cas d'utilisation, la réponse n'est pas forcément si évidente qu'elle puisse paraitre. Dans cet article je parlerai surtout de MySQL mais les principes peuvent s'appliquer à n'importe quelle base de données. Je ne parlerai également, dans un soucis de simplicité, que des cas on l'on souhaite stocker une date et une heure, la cas d'une date seule étant seulement une version simplifiée de celle-ci.

La réponse la plus évidente: Datetime

Instinctivement, et ce jusqu'à il y a quelques années, j'utilisais le type datetime sans me poser de question. En effet, il s'utilise très facilement, son format se rapproche beaucoup de celui de PHP et il permet de nombreuses manipulations (je vous conseille d'ailleurs les deux autres articles sur la manipulation de date en PHP et SQL ).
Mais, en y regardant de plus près, le champ datetime n'est qu'une chaine de caractère et toutes les données concernant une date ne sont pas renseignées.
En effet, sans le contexte de l'enregistrement, lorsque l'on récupère un datetime en base, il n'y a aucun moyen de savoir dans quelle timezone est cette date. Et même si nous connaissons la timezone (à partir d'un autre champ par exemple), s'il faut l'afficher dans une autre timezone, ce n'est pas forcément une manipulation aisée.

Le faux ami: Timestamp

On pourrait croire que le type timestamp est la solution, mais en fait son nom est trompeur. La façon la plus simple de l'expliquer est de montrer son fonctionnement par un exemple:

MariaDB [test]> CREATE TABLE `Test` (
    ->   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `timestamp` timestamp NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)
 
 
MariaDB [test]> INSERT Test (timestamp) VALUES (1515359139);
Query OK, 1 row affected, 1 warning (0.01 sec)
 
MariaDB [test]> INSERT Test (timestamp) VALUES ('2018-01-07 22:10');
Query OK, 1 row affected, 1 warning (0.02 sec)
 
MariaDB [test]> SELECT * FROM Test;
+----+---------------------+
| id | timestamp           |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
|  2 | 2018-01-07 22:10:00 |
+----+---------------------+
3 rows in set (0.00 sec)

Comme vous pouvez le constater, malgré son nom, le champs timestamp se comporte exactement comme le champ datetime. Il ne prend pas un timestamp en entrée et ne renvoie pas un timestamp.
En réalité, en lisant la documentation, on se rend compte que la différence entre datetime et timestamp réside dans la façon de stocker la donnée en interne dans SQL. Dans le cas du timestamp, MySQL convertit la date en timestamp pour le stockage et la retraduit lorsque la donnée est demandée. Pour cela, MySQL utilise sa timezone courante (variable time_zone). Ce qui signifie que, si la configuration du serveur est modifiée entre l'enregistrement et la recherche, la valeur de la date sera modifiée.
De plus, au final, ce format ne permet pas non plus de stocker des informations sur la timezone d'enregistrement.

Un autre problème de ce format, est qu'il est limité à la taille du int, ce qui signifie que l'on ne peut stocker une date au delà de 2038.

MariaDB [test]> INSERT Test (timestamp) VALUES ('2040-01-01 10:10');
Query OK, 1 row affected (0.01 sec)
 
MariaDB [test]> SELECT * FROM Test;
+----+---------------------+
| id | timestamp           |
+----+---------------------+
|  3 | 0000-00-00 00:00:00 |
+----+---------------------+
1 rows in set (0.00 sec)

Alternative: timestamp comme un integer

Une solution est de ne pas demander à votre système de stockage de données d'être intelligent. Après tout, son but est de stocker nos informations de manière fiable, éventuellement ACID mais pas de deviner dans quelle timezone sont nos utilisateurs.
L'idée est donc de stocker nos dates en tant que timestamp dans un simple champ integer et c'est à l'application de gérer cette date. En réalité on utilise un big int, sinon le prochain bug de l'an 2000 sera le bug du "2038-01-09 03:14:07" (valeur max du int en UTC).
Et pour avoir une valeur lisible lors d'une requête SQL, il suffit d'utiliser la fonction FROM_UNIXTIME.

MariaDB [test]> CREATE TABLE `Test` (
    ->   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `timestamp` bigint UNSIGNED NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)
 
MariaDB [test]> INSERT Test (timestamp) VALUES (1515359139);
Query OK, 1 row affected (0.02 sec)
 
MariaDB [test]> INSERT Test (timestamp) VALUES (UNIX_TIMESTAMP());
Query OK, 1 row affected (0.01 sec)
 
MariaDB [test]> INSERT Test (timestamp) VALUES (253402210800);
Query OK, 1 row affected (0.01 sec)
 
MariaDB [test]> SELECT timestamp FROM Test;
+----------------+
| timestamp      |
+----------------+
|     1515359139 |
|     1515362870 |
|   253402210800 |
+-----------------+
3 rows in set (0.00 sec)
 
MariaDB [test]> SELECT from_unixtime(timestamp) FROM Test;
+--------------------------+
| from_unixtime(timestamp) |
+--------------------------+
| 2018-01-07 22:05:39      |
| 2018-01-07 23:07:50      |
| NULL                     |
+--------------------------+
3 rows in set (0.00 sec)

Attention toutefois, les fonctions sur les dates de MySQL ne fonctionnent pas pour les valeurs plus grande que 32bits.
Heureusement notre langage préféré (PHP) s'en sort mieux.

$ php -r "echo (new DateTime('@253402210800'))->format('Y-m-d');"
9999-12-31

Avantages

Ce système permet de laisser la gestion du format d'affichage à l'application front et d'avoir une base de données plutôt idiote, car au final son rôle est de stocker nos données, pas d'avoir d'intelligence métier.

Un exemple simple de problème d'affichage en datetime est la gestion des commentaires sur ce blog. Si quelqu'un sur l'île de la Réunion publie un message disons le 27 janvier à 1h du matin (un insomniaque), en enregistre '2018-01-27 01:00:00', en France il n'est que 22h, je réponds rapidement et ma réponse est enregistrer à '2018-01-26 22:30:00'. Ce qui fait qu'à l'affichage la réponse sera avec la question! Vous me direz que si mon site ne gère pas les timezones et est toujours en France l'ordre sera bon, mais mon lecteur verra son message écrit avec un jour d'avance, ce qui n'est pas terrible non plus.
Si toutes les dates sont enregistrées en base en timestamp, la logique de l'affichage se fait côté front en fonction de la localisation de l'utilisateur et tout le monde est content.

Inconvénients

Si vous avez l'habitude de laisser votre SGBD remplir vos champs de date de création lui-même, ce n'est pas possible en enregistrant dans un champ integer. En effet, le default current_timestamp ne fonctionne que pour les champs de type datetime et timestamp.

De plus, selon le type de données que vous enregistrez, parfois l'affichage d'une date ne dépend pas de la localisation de l'utilisateur mais de la données elle-même. Dans ce genre de cas il faut ruser un peu.

Prenons comme exemple un site événementiel qui annonce différents spectacles, concerts ou festivals.
La différence par rapport au précédent exemple est que cette fois ci, l'horaire n'a de sens que par rapport au lieu où il a lieu et n'est plus lié à l'utilisateur.
Dans cette situation la timezone de la date dépend des données insérées et est donc fixe pour chaque évènement. Le plus simple est de stocker le timestamp et la timezone dans deux colonnes séparées afin de pouvoir récupérer une date complète avec toutes les informations.
Ce qui donnerait en base:

MariaDB [test]> CREATE TABLE event(
    ->   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(100) NOT NULL,
    ->   `place` varchar(100) NOT NULL,
    ->   `date_ts` bigint(20) UNSIGNED NOT NULL,
    ->   `tz` varchar(50) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> INSERT event (name, place, date_ts, tz) VALUES ('Metallica', 'Accor Hotel Arena, Paris', 1504800000, 'Europe/Paris');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [test]> SELECT * FROM event;
+----+-----------+--------------------------+------------+--------------+
| id | name      | place                    | date_ts    | tz           |
+----+-----------+--------------------------+------------+--------------+
|  1 | Metallica | Accor Hotel Arena, Paris | 1504800000 | Europe/Paris |
+----+-----------+--------------------------+------------+--------------+
1 row in set (0.00 sec)
 

Et lors de la récupération de la donnée:

<?php
 
$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', null);
$sql =  'SELECT name, place, date_ts, tz FROM event LIMIT 1';
foreach  ($conn->query($sql) as $row) {
    $date = new DateTime('@'.$row['date_ts']); // si le premier paramètre est un timestamp, le second paramètre est ignoré
    $date->setTimezone(new DateTimeZone($row['tz']));
    var_dump($date);
}
/*
class DateTime#4 (3) {
  public $date =>
  string(26) "2017-09-07 18:00:00.000000"
  public $timezone_type =>
  int(3)
  public $timezone =>
  string(12) "Europe/Paris"
}
*/

Autres idées: les chaînes de caractères

Tant qu'on y est à utiliser des types de champs primaires pour stocker nos dates, pourquoi ne pas carrément stocker en tant que chaîne de caractères.

Timestamp dans un varchar

Comme l'integer, de par sa taille, limite le timestamp que l'on peut enregistrer, on peut se dire que le stocker dans un varchar supprimerait cette limite.
Mais le bigint permet d'aller plus loin que le DateTime (9999-12-31), ce qui est largement suffisant. D'ici là, l'informatique aura forcément évoluée, rendant ces questions de format de date obsolètes.
Ensuite, le format varchar est plus gourmant en mémoire. Un bigint est stocké sur 4 bits tandis que le varchar utilise autant de bits que le nombre de caractères plus 1, soit par exemple 21bits pour un varchar(20).
Ce format permet d'utiliser les fonctions natives de MySQL comme from_unixtime, j'ai cependant quelques doutes sur la performances du trie ou de la recherche d'une date supérieure ou inférieure à une autre (ce qui est très performant avec des integers).
Au final cette solution est très proche et plus gourmande que celle avec des integers et a donc peu d'intérêt.

DateTime complet dans un varchar

Pour résoudre le problème du manque d'information sur la timezone dans un champ DateTime, nous pourrions également stocker la date au format ISO 8601 (1997-07-16T19:20:30.45+01:00) ou un autre standard dans un champ varchar.
Attention toutefois, si le format ISO 8601 permet de trier en fonction de la date, car le classement par ordre lexicographique correspond à l'ordre chronologique, la sélection de lignes en fonction d'un date n'est pas possible.
Il faut bien connaitre ses cas d'utilisation avant d'envisager cette solution.

MariaDB [test]> CREATE TABLE `test` (
    ->  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  `timestamp` varchar(50) NOT NULL,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> INSERT test (timestamp) VALUES ('1997-07-16T19:20:30.45+01:00');
Query OK, 1 row affected (0.01 sec)
 
MariaDB [test]> INSERT test (timestamp) VALUES ('2004-02-12T15:19:21+00:00');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [test]> INSERT test (timestamp) VALUES ('2004-02-12T15:19:00-05:00');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [test]> INSERT test (timestamp) VALUES ('2004-02-12T15:19:21+01:00');
Query OK, 1 row affected (0.01 sec)
 
MariaDB [test]> select * from test order by timestamp;
+----+------------------------------+
| id | timestamp                    |
+----+------------------------------+
|  1 | 1997-07-16T19:20:30.45+01:00 |
|  3 | 2004-02-12T15:19:00-05:00    |
|  2 | 2004-02-12T15:19:21+00:00    |
|  4 | 2004-02-12T15:19:21+01:00    |
+----+------------------------------+
3 rows in set (0.00 sec)
 
MariaDB [test]> select * from test where timestamp > '2004-02-12T15:19';
+----+---------------------------+
| id | timestamp                 |
+----+---------------------------+
|  2 | 2004-02-12T15:19:21+00:00 |
|  3 | 2004-02-12T15:19:00-05:00 |
|  4 | 2004-02-12T15:19:21+01:00 |
+----+---------------------------+
2 rows in set (0.00 sec)

Conclusion

J'espère que cela vous aura aidé à y voir un peu plus clair et à identifier quelques points particuliers.
Pour finir, je ne penses pas qu'il y ait de solution parfaite et que cela dépend avant tout de vos cas d'utilisation.
Mais si je peux conseiller une seule chose, c'est de décider d'un format et de s'y tenir pour toute votre base. Il n'y a rien de pire à gérer et utiliser qu'une base de données non homogène dans ces types de champs ou dans sa nomenclature (et je sais de quoi je parles).