Manipulation des dates et heures dans les requêtes SQL avec MySQL
Manipuler les dates dans un langage de programmation (PHP, java....) est simple et connu de tous. Mais qu'en est il dans la base de donnée MySQL? Dans cet article je présente ce qu'il est possible de faire dans les requêtes SQL: opération, intervalle de date... Je tag volontairement cet article avec PHP bien qu'il ne traite pas du sujet, mais je pense qu'il sera plus utile aux développeurs qu'aux DBA.
Pour cet article j'utilise un champ de type "DATETIME", la plus part des fonctions présentées sont utilisables sur des champs de type "DATE" ou "TIME" ou à défault des fonctions analogues existent.
Extraction dans un champ datetime
Le format d'un champ "DATETIME" dans MySQL est "YYYY-mm-dd hh:ii:ss". Il est possible d'extraire chaque partie des dates avec des fonctions basiques DATE(), TIME(), YEAR().......
SELECT TS, DATE(TS), YEAR(TS), MONTH(TS), DAY(TS), TIME(TS), HOUR(TS), MINUTE(TS), SECOND(TS) FROM MA_TABLE LIMIT 1; +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+ | TS | DATE(TS) | YEAR(TS) | MONTH(TS) | DAY(TS) | TIME(TS) | HOUR(TS) | MINUTE(TS) | SECOND(TS) | +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+ | 2012-09-01 16:00:30 | 2012-09-01 | 2012 | 9 | 1 | 16:00:30 | 16 | 0 | 30 | +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+ 1 row in set (0.00 sec)
Ces fonctions sont accessibles sans restriction et peuvent être utilisées dans toutes les parties d'une requête SQL: SELECT, ON, WHERE, GROUP BY....
On peut réaliser d'autres extractions un peu plus complexes avec la fonction EXTRACT() pour des cas plus particuliers.
SELECT TS, EXTRACT(YEAR_MONTH FROM TS), EXTRACT(DAY_MINUTE FROM TS) FROM MA_TABLE LIMIT 1; +---------------------+-----------------------------+-----------------------------+ | TS | EXTRACT(YEAR_MONTH FROM TS) | EXTRACT(DAY_MINUTE FROM TS) | +---------------------+-----------------------------+-----------------------------+ | 2012-09-01 16:00:30 | 201209 | 11600 | +---------------------+-----------------------------+-----------------------------+ 1 row in set (0.00 sec)
Changer le format d'une date
Il est possible de modifier le format d'une date avec la fonction DATE_FORMAT().
SELECT TS, DATE_FORMAT(TS, '%Y %M %d'), DATE_FORMAT(TS, '%d-%m-%y %h:%i:%s') FROM MA_TABLE LIMIT 1; +---------------------+-----------------------------+--------------------------------------+ | TS | DATE_FORMAT(TS, '%Y %M %d') | DATE_FORMAT(TS, '%d-%m-%y %h:%i:%s') | +---------------------+-----------------------------+--------------------------------------+ | 2012-09-01 16:30:14 | 2012 September 01 | 01-09-12 04:30:14 | +---------------------+-----------------------------+--------------------------------------+ 1 row in set (0.00 sec)
En général, on utilise toujours les mêmes formats pour afficher les dates. Comme les choses sont biens faites, MySQL propose des formats pédéfinis accessibles par la fonction GET_FORMAT().
SELECT TS, DATE_FORMAT(TS, GET_FORMAT(DATE, 'EUR')), DATE_FORMAT(TS, GET_FORMAT(DATE, 'USA')), DATE_FORMAT(TS, GET_FORMAT(DATETIME, 'ISO')) FROM MA_TABLE LIMIT 1; +---------------------+------------------------------------------+------------------------------------------+----------------------------------------------+ | TS | DATE_FORMAT(TS, GET_FORMAT(DATE, 'EUR')) | DATE_FORMAT(TS, GET_FORMAT(DATE, 'USA')) | DATE_FORMAT(TS, GET_FORMAT(DATETIME, 'ISO')) | +---------------------+------------------------------------------+------------------------------------------+----------------------------------------------+ | 2012-09-01 16:30:14 | 01.09.2012 | 09.01.2012 | 2012-09-01 16:30:14 | +---------------------+------------------------------------------+------------------------------------------+----------------------------------------------+
Le premier paramètre de la fonction GET_FORMAT() doit être DATE, DATETIME ou TIME et permet de définir quelle partie on veut afficher.
Opération sur une date
Les opérations arithmétiques sont bien sûr possible. Plusieurs fonctions et notations permettent d'obtenir le même résultat. Si je veux ajouter 5 jours à une date, je peux utiliser les fonctions ADDDATE(), DATE_ADD() ou aucune des deux.
SELECT TS, ADDDATE(TS, 5), ADDDATE(TS, INTERVAL 5 DAY), DATE_ADD(TS, INTERVAL 5 DAY), TS + INTERVAL 5 DAY FROM MA_TABLE LIMIT 1; +---------------------+---------------------+-----------------------------+------------------------------+---------------------+ | TS | ADDDATE(TS, 5) | ADDDATE(TS, INTERVAL 5 DAY) | DATE_ADD(TS, INTERVAL 5 DAY) | TS + INTERVAL 5 DAY | +---------------------+---------------------+-----------------------------+------------------------------+---------------------+ | 2012-09-02 16:00:23 | 2012-09-07 16:00:23 | 2012-09-07 16:00:23 | 2012-09-07 16:00:23 | 2012-09-07 16:00:23 | +---------------------+---------------------+-----------------------------+------------------------------+---------------------+ 1 row in set (0.00 sec)
La première notation de la fonction ADDDATE() ne permet que d'ajouter un nombre de jour. L'utilisation du mot clef "INTERVAL", dans les autres notations, permet de créer des intervalles de temps plus complexes.
SELECT TS, TS + INTERVAL '1 10' YEAR_MONTH, TS + INTERVAL 1 MONTH + INTERVAL 10 DAY FROM MA_TABLE LIMIT 1; +---------------------+---------------------------------+-----------------------------------------+ | TS | TS + INTERVAL '1 10' YEAR_MONTH | TS + INTERVAL 1 MONTH + INTERVAL 10 DAY | +---------------------+---------------------------------+-----------------------------------------+ | 2012-09-02 16:30:33 | 2014-07-02 16:30:33 | 2012-10-12 16:30:33 | +---------------------+---------------------------------+-----------------------------------------+ 1 row in set (0.00 sec)
Bien sûr on peut faire la même chose pour une soustraction avec les fonctions SUBDATE(), DATE_SUB() ou en spécifiant une valeur négative.
Enfin, il est possible de calculer la différence entre deux dates ou deux heures avec les fonction DATEDIFF() et TIMEDIFF(). DATEDIFF() ne renvoie qu'un nombre de jour. Les deux fonctions ne savent travailler que sur leur partie respective.
SELECT DATEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00'), TIMEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00'); +--------------------------------------------------------+--------------------------------------------------------+ | DATEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00') | TIMEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00') | +--------------------------------------------------------+--------------------------------------------------------+ | 6 | 132:37:00 | +--------------------------------------------------------+--------------------------------------------------------+ 1 row in set (0.00 sec)
Pour obtenir le différentiel dans d'autres unités il faut utiliser la fonction TIMESTAMPDIFF(). Attention aux paramêtres, l'ordre des deux dates est inversé par rapport aux fonctions précédentes.
SELECT TIMESTAMPDIFF(MINUTE, '2012-12-25 23:59:00', '2012-12-31 12:36:00'); +---------------------------------------------------------------------+ | TIMESTAMPDIFF(MINUTE, '2012-12-25 23:59:00', '2012-12-31 12:36:00') | +---------------------------------------------------------------------+ | 7957 | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)
Convertir une date
Il existe tout un panel de fonctions pour convertir les dates:
- TO_DAYS() : obtenir le nombre de jour depuis l'an 0
- TIME_TO_SEC(): convertir l'heure en nombre de secondes
- DAYOFYEAR(): obtenir le jour de l'année
.....
Et si la date est stockée dans un champ de type autre que "DATE", DATETIME" ou "TIME" il est toujours possible d'utiliser la fonction CAST() pour pouvoir utiliser les nombreuses fonctions de manipulation de date.
SELECT CAST('2012-12-25' AS DATETIME); +--------------------------------+ | CAST('2012-12-25' AS DATETIME) | +--------------------------------+ | 2012-12-25 00:00:00 | +--------------------------------+ 1 row in set (0.00 sec)
Et si la date est sous un format différent, il est dans ce cas possible d'utiliser la fonction STR_TO_DATE(), qui permet d'indiquer à MySQL le format de la date passée en paramêtre.
SELECT STR_TO_DATE('25/12/2012', '%d/%m/%Y'), CAST('25/12/2012' AS DATE); +---------------------------------------+----------------------------+ | STR_TO_DATE('25/12/2012', '%d/%m/%Y') | CAST('25/12/2012' AS DATE) | +---------------------------------------+----------------------------+ | 2012-12-25 | NULL | +---------------------------------------+----------------------------+ 1 row in set, 1 warning (0.00 sec)
J'espère que cet article vous aura fait décourvrir ou redécouvrir des fonctions très pratiques du langague SQL. En suivant ce lien vous trouverez la documentation des fonctions de manipulation des dates et heures.
Réponse de Ulrich le 5 nov. 2017