• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

Vous pouvez obtenir un certificat de réussite à l'issue de ce cours.

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 20/02/2019

Calculs sur les données temporelles

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

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

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.

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;

nb_jours

45

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.

-- 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;

datetime1

datetime2

difference

2011-10-08 12:35:45

2011-10-07 16:00:25

20:35:20

time1

time2

difference

12:35:45

00:00:25

12:35:20

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'.

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;

nb_jours

nb_heures_def

nb_heures

nb_trimestres

45

1102

1088

3

Ajout et retrait d'un intervalle de temps

Intervalle

Certaines des fonctions et opérations suivantes utilisent le mot-clé INTERVALpermettant 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.

Unité

Format

SECOND

-

MINUTE

-

HOUR

-

DAY

-

WEEK

-

MONTH

-

YEAR

-

MINUTE_SECOND

'm:S'

HOUR_SECOND

'HH:mm:SS'

HOUR_MINUTE

'HH:mm'

DAY_SECOND

'J HH:mm:SS'

DAY_MINUTE

'J HH:mm'

DAY_HOUR

'J HH'

YEAR_MONTH

'A-M'

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 :

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_interval

datetime_interval

date_nombre_jours

datetime_nombre_jours

2011-08-21

2011-05-24 14:26:28

2011-06-02

2011-07-02 12:15:56

DATE_ADD()

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

Exemples :

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

avec_date

avec_datetime

2011-08-21

2011-05-24 14:26:28

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 :

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

droite

gauche

2011-05-26

2011-05-25 00:15:56

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_SECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTER, et YEAR.

Exemple :

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

avec_date

avec_datetime

2011-05-26

2011-05-21 12:49:56

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 :

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

Maintenant

DansUneHeure

HeureCourante

PlusTard

2012-04-29 12:08:33

2012-04-29 13:08:33

12:08:33

15:28:35

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 :

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;

SUBDATE1

SUBDATE2

DATE_SUB

2011-05-18 10:05:24

2011-05-09

2011-02-21

SUBTIME1

SUBTIME2

2011-05-20 17:40:41

03:40:41

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 :

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

'2011-05-21' - INTERVAL 5 DAY

2011-05-16

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.

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;

ajout_negatif

retrait_positif

2011-09-01 16:04:26

2011-09-01 16:04:26

ajout_positif

retrait_negatif

2011-12-01 20:04:26

2011-12-01 20:04:26

ajout_negatif

retrait_positif

2011-12-01 15:49:28

2011-12-01 15:49:28

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.

SELECT FROM_UNIXTIME(1325595287);

FROM_UNIXTIME(1325595287)

2012-01-03 13:54:47

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.

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

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

1325595287

À 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.

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

60eJour2012

heureCree

2012-02-29

03:45:34

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 :

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

SEC_TO_TIME(102569)

TIME_TO_SEC('01:00:30')

28:29:29

3630

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 2016 est une année bissextile, contrairement à l'an 2100.

SELECT LAST_DAY('2016-02-03') AS fevrier2016, LAST_DAY('2100-02-03') AS fevrier2100;

fevrier2016

fevrier2100

2016-02-29

2100-02-28

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 DATETIMEADDTIME()  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 DATETIMESUBTIME()  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.

Exemple de certificat de réussite
Exemple de certificat de réussite