Calculs sur les données temporelles

• Formater une donnée temporelle
• Sommaire
• Exercices

Il est fréquent de vouloir faire des calculs sur des données temporelles. Par exemple, pour calculer le nombre de jours ou d'heures entre deux dates, pour ajouter une certaine durée à une donnée en cas de calcul d'échéance, etc. Pour ce faire, on peut soit se lancer dans des calculs compliqués en convertissant des jours en heures, des heures en jours, des minutes en secondes ; soit utiliser les fonctions SQL prévues à cet effet. Je vous laisse deviner quelle solution est la meilleure…

Nous allons donc voir dans ce chapitre comment :

• calculer la différence entre deux données temporelles ;
• ajouter un intervalle de temps à une donnée temporelle ;
• convertir une donnée horaire en un nombre de secondes ;
• et d'autres petites choses bien utiles.

• Différence entre deux dates/heures
• Ajout et retrait d'un intervalle de temps
• Divers

Différence entre deux dates/heures

Trois fonctions permettent de calculer le temps écoulé entre deux données temporelles :

• DATEDIFF() : qui donne un résultat en nombre de jours ;
• TIMEDIFF() : qui donne un résultat sous forme de TIME ;
• TIMESTAMPDIFF() : qui donne le résultat dans l'unité de temps souhaitée (heure, secondes, mois,…).

DATEDIFF()

DATEDIFF(date1, date2) peut s'utiliser avec des données de type DATE ou DATETIME (dans ce dernier cas, seule la partie date est utilisée).

Les trois requêtes suivantes donnent donc le même résultat.

1
2
3

SELECT DATEDIFF('2011-12-25','2011-11-10') AS nb_jours;
SELECT DATEDIFF('2011-12-25 22:12:18','2011-11-10 12:15:41') AS nb_jours;
SELECT DATEDIFF('2011-12-25 22:12:18','2011-11-10') AS nb_jours;

TIMEDIFF()

La fonction TIMEDIFF(expr1, expr2) calcule la durée entre expr1 et expr2. Les deux arguments doivent être de même type, soit TIME, soit DATETIME.

1
2
3
4
5

-- Avec des DATETIME
SELECT '2011-10-08 12:35:45' AS datetime1, '2011-10-07 16:00:25' AS datetime2, TIMEDIFF('2011-10-08 12:35:45', '2011-10-07 16:00:25') as difference;

-- Avec des TIME
SELECT '12:35:45' AS time1, '00:00:25' AS time2, TIMEDIFF('12:35:45', '00:00:25') as difference;

TIMESTAMPDIFF()

La fonction TIMESTAMPDIFF() prend, quant à elle, un paramètre supplémentaire : l'unité de temps désirée pour le résultat. Les unités autorisées comprennent : SECOND (secondes), MINUTE (minutes), HOUR (heures), DAY (jours), WEEK (semaines), MONTH (mois), QUARTER (trimestres) et YEAR (années).

TIMESTAMPDIFF(unite, date1, date2) s'utilise également avec des données de type DATE ou DATETIME. Si vous demandez un résultat comprenant une unité inférieure au jour (heure ou minute par exemple) et que l'une de vos données est de type DATE, MySQL complétera cette date avec l'heure par défaut '00:00:00'.

1
2
3
4

SELECT TIMESTAMPDIFF(DAY, '2011-11-10', '2011-12-25') AS nb_jours,
       TIMESTAMPDIFF(HOUR,'2011-11-10', '2011-12-25 22:00:00') AS nb_heures_def, 
       TIMESTAMPDIFF(HOUR,'2011-11-10 14:00:00', '2011-12-25 22:00:00') AS nb_heures,
       TIMESTAMPDIFF(QUARTER,'2011-11-10 14:00:00', '2012-08-25 22:00:00') AS nb_trimestres;

Ajout et retrait d'un intervalle de temps

Intervalle

Certaines des fonctions et opérations suivantes utilisent le mot-clé INTERVAL, permettant de définir un intervalle de temps à ajouter ou à soustraire d'une date.

Un intervalle de temps est défini par une quantité et une unité ("3 jours" par exemple, "3" étant la quantité, "jour" l'unité). En MySQL, il existe une vingtaine d'unités possibles pour un INTERVAL, dont une partie est listée dans le tableau ci-dessous.

Notez que, comme pour DATE, DATETIME et TIME, les signes de ponctuation séparant les différentes parties d'un intervalle ne doivent pas nécessairement être '-' pour la partie date et ':' pour la partie heure. Il ne s'agit que de suggestions. N'importe quel signe de ponctuation (ou aucun) sera accepté.

Ajout d'un intervalle de temps

Trois fonctions permettent d'ajouter un intervalle de temps à une date (de type DATE ou DATETIME) :

• ADDDATE() : qui s'utilise avec un INTERVAL ou un nombre de jours.
• DATE_ADD() : qui s'utilise avec un INTERVAL.
• TIMESTAMPADD() : qui n'utilise pas d'INTERVAL mais un nombre plus limité d'unités de temps.

ADDDATE()

Cette fonction peut s'utiliser de deux manières : soit en précisant un intervalle de temps avec le mot-clé INTERVAL, soit en donnant un nombre de jours à ajouter à la date.

• ADDDATE(date, INTERVAL quantite unite)
• ADDDATE(date, nombreJours)

Exemples :

1
2
3
4

SELECT ADDDATE('2011-05-21', INTERVAL 3 MONTH) AS date_interval,                              -- Avec DATE et INTERVAL
       ADDDATE('2011-05-21 12:15:56', INTERVAL '3 02:10:32' DAY_SECOND) AS datetime_interval, -- Avec DATETIME et INTERVAL
       ADDDATE('2011-05-21', 12) AS date_nombre_jours,                                        -- Avec DATE et nombre de jours
       ADDDATE('2011-05-21 12:15:56', 42) AS datetime_nombre_jours;                           -- Avec DATETIME et nombre de jours

DATE_ADD()

DATE_ADD(date, INTERVAL quantite unite) s'utilise exactement de la même manière que ADDDATE(date, INTERVAL quantite unite).

Exemples :

1
2

SELECT DATE_ADD('2011-05-21', INTERVAL 3 MONTH) AS avec_date,                               -- Avec DATE
       DATE_ADD('2011-05-21 12:15:56', INTERVAL '3 02:10:32' DAY_SECOND) AS avec_datetime;  -- Avec DATETIME

Opérateur +

Il est également possible d'ajouter un intervalle de temps à une date en utilisant simplement l'opérateur + et un INTERVAL. L'intervalle peut se trouver à droite ou à gauche du signe +.

Exemples :

1
2

SELECT '2011-05-21' + INTERVAL 5 DAY AS droite,                    -- Avec DATE et intervalle à droite
       INTERVAL '3 12' DAY_HOUR + '2011-05-21 12:15:56' AS gauche; -- Avec DATETIME et intervalle à gauche

TIMESTAMPADD()

TIMESTAMPADD(unite, quantite, date) est un peu plus restreint que DATE_ADD() et ADDDATE(). En effet, cette fonction n'utilise pas d'INTERVAL. Il faut cependant définir une unité parmi les suivantes : FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, et YEAR.

Exemple :

1
2

SELECT TIMESTAMPADD(DAY, 5, '2011-05-21') AS avec_date,                   -- Avec DATE
       TIMESTAMPADD(MINUTE, 34, '2011-05-21 12:15:56') AS avec_datetime;  -- Avec DATETIME

ADDTIME()

La fonction ADDTIME(expr1, expr2) permet d'ajouter expr2 (de type TIME) à expr1 (de type DATETIME ou TIME). Le résultat sera du même type que expr1.

Exemples :

1
2

SELECT NOW() AS Maintenant, ADDTIME(NOW(), '01:00:00') AS DansUneHeure,                  -- Avec un DATETIME
       CURRENT_TIME() AS HeureCourante, ADDTIME(CURRENT_TIME(), '03:20:02') AS PlusTard; -- Avec un TIME

Soustraction d'un intervalle de temps

SUBDATE(), DATE_SUB() et SUBTIME()

SUBDATE(), DATE_SUB() et SUBTIME() sont les équivalents de ADDDATE(), DATE_ADD() et ADDTIME() pour la soustraction. Elles s'utilisent exactement de la même manière.

Exemples :

1
2
3
4
5
6

SELECT SUBDATE('2011-05-21 12:15:56', INTERVAL '3 02:10:32' DAY_SECOND) AS SUBDATE1, 
       SUBDATE('2011-05-21', 12) AS SUBDATE2,
       DATE_SUB('2011-05-21', INTERVAL 3 MONTH) AS DATE_SUB;

SELECT SUBTIME('2011-05-21 12:15:56', '18:35:15') AS SUBTIME1,
       SUBTIME('12:15:56', '8:35:15') AS SUBTIME2;

Opérateur -

Tout comme l'opérateur + peut s'utiliser pour ajouter un intervalle de temps, il est possible d'utiliser l'opérateur - pour en soustraire un. Cependant, pour la soustraction, la date doit impérativement se trouver à gauche du signe -, et l'intervalle à droite. Il n'est en effet pas possible de soustraire une date d'un intervalle.

Exemple :

1

SELECT '2011-05-21' - INTERVAL 5 DAY;

Soustraire, c'est ajouter un négatif

Un INTERVAL peut être défini avec une quantité négative, et ajouter un intervalle négatif, c'est soustraire un intervalle positif. De même soustraire un intervalle négatif revient à ajouter un intervalle positif.

Par conséquent, dans les requêtes suivantes, les deux parties du SELECT sont équivalentes.

1
2
3

SELECT ADDDATE(NOW(), INTERVAL -3 MONTH) AS ajout_negatif, SUBDATE(NOW(), INTERVAL 3 MONTH) AS retrait_positif;
SELECT DATE_ADD(NOW(), INTERVAL 4 HOUR) AS ajout_positif, DATE_SUB(NOW(), INTERVAL -4 HOUR) AS retrait_negatif;
SELECT NOW() + INTERVAL -15 MINUTE AS ajout_negatif, NOW() - INTERVAL 15 MINUTE AS retrait_positif;

Divers

Créer une date/heure à partir d'autres informations

À partir d'un timestamp Unix

La fonction FROM_UNIXTIME(ts) renvoie un DATETIME à partir du timestamp Unix ts.

1

SELECT FROM_UNIXTIME(1325595287);

Notez que la fonction UNIX_TIMESTAMP(), que nous avons vue lors d'un chapitre précédent et qui donne le timestamp actuel, peut également s'utiliser avec un DATETIME en paramètre ; dans ce cas, elle fait l'inverse de la fonction FROM_UNIXTIME(ts) : elle renvoie le timestamp Unix du DATETIME passé en paramètre.

1

SELECT UNIX_TIMESTAMP('2012-01-03 13:54:47');

À partir de différents éléments d'une date/heure

La fonction MAKEDATE() crée une DATE à partir d'une année et d'un numéro de jour (1 étant le premier janvier, 32 le premier février, etc.). Quant à la fonction MAKETIME(), elle crée un TIME à partir d'une heure et d'un nombre de minutes et de secondes.

1

SELECT MAKEDATE(2012, 60) AS 60eJour2012, MAKETIME(3, 45, 34) AS heureCree;

Convertir un TIME en secondes, et vice versa

Il est toujours utile de connaître la fonction SEC_TO_TIME()qui convertit un nombre de secondes en une donnée de type TIME, et son opposé TIME_TO_SEC()qui convertit un TIME en un nombre de secondes.

Exemples :

1

SELECT SEC_TO_TIME(102569), TIME_TO_SEC('01:00:30');

Dernier jour du mois

Enfin, voici la dernière fonction que nous verrons dans ce chapitre : LAST_DAY(date). Cette fonction donne le dernier jour du mois de la date passée en paramètre. Cela permet par exemple de voir que 2012 est une année bissextile, contrairement à l'an 2100.

1

SELECT LAST_DAY('2012-02-03') AS fevrier2012, LAST_DAY('2100-02-03') AS fevrier2100;

En résumé

• DATEDIFF(), TIMEDIFF() et TIMESTAMPDIFF() sont trois fonctions permettant de calculer une différence entre deux données temporelles.
• Un INTERVAL SQL représente un intervalle de temps ; il est composé d'une quantité et d'une unité de temps (ex. : INTERVAL 3 DAY représente un intervalle de trois jours).
• ADDDATE() et DATE_ADD() permettent d'ajouter un intervalle de temps à une DATE ou une DATETIME. ADDTIME() permet d'ajouter une durée à un TIME ou une DATETIME.
• SUBDATE() et DATE_SUB() permettent de soustraire un intervalle de temps à une DATE ou une DATETIME. SUBTIME() permet de soustraire une durée à un TIME ou une DATETIME.
• On peut également utiliser les opérateurs + et - pour ajouter ou soustraire un intervalle de temps à une donnée temporelle.

• Formater une donnée temporelle
• Sommaire
• Exercices