Fonctions de date/heure en MySQL


précédentsommairesuivant

1. Fonctions de dates et heures

Cette section présente les fonctions de date/heures les plus couramment utilisées.

Pour plus de détails ou pour d'autres fonctions, veuillez vous référer à la documentation propre à votre version de MySQL :
3.23, 4.0, 4.15.05.1

Comme annoncé en introduction, les fonctions de dates disponibles dépendent de votre version de MySQL. Pour chaque fonction de date/heures, les restrictions liées à la version de MySQL sont précisées, avec éventuellement un lien vers la section Rétrocompatibilité lorsque des solutions alternatives existent.

1.1. Représentation des dates

A l'affichage, suivant le contexte, les dates et heures peuvent être représentées:

  • Sous forme de chaînes, au format suivant: Pour les dates: "YYYY-MM-DD"Pour les heures: "hh:mm:ss"Pour les date-heures: "YYYY-MM-DD hh:mm:ss"

  • Sous forme d'entiers, de la forme : Pour les dates: YYYYMMDDPour les heures: hhmmssPour les date-heures: YYYYMMDDhhmmss

Par défaut, les dates/heures sont représentées sous forme de chaînes.



Représentation sous forme de chaîne :

 
Sélectionnez
SELECT NOW();
-> '2006-01-14 15:43:02'

Représentation sous forme d'entier :

 
Sélectionnez
SELECT NOW() + 0;
-> 20060114154302

1.2. Date/Heure actuelle : fonctions CURDATE(), CURTIME(), NOW() et SYSDATE()

Voici les fonctions qui vont vous permettre de connaître les date et heure courantes :

Nom Description Restrictions
de version
CURDATE() CURDATE() retourne la date actuelle (ex: "2006-01-14").

Lorsque CURDATE() est affectée à un champ de type DATETIME, l'heure 00:00:00 est automatiquement ajoutée (ex: "2006-01-14 00:00:00"), ce qui correspond à la date du jour, à minuit.
 
CURTIME()

CURTIME() retourne l'heure actuelle (ex: "15:41:32").


 
NOW()

NOW() retourne la date et l'heure actuelles (ex: "2006-01-14 15:41:32").


 
SYSDATE() SYSDATE() retourne la date et l'heure courante.

La différence avec NOW() est que SYSDATE() retourne l'heure à laquelle elle est effectivement appelée, alors que NOW() retourne l'heure de début de script.
 

1.3. Formatage d'une date : fonction DATE_FORMAT()

La fonction DATE_FORMAT permet le formatage d'une date dans le format désiré.

Sa syntaxe est la suivante : DATE_FORMAT(date, format)

Voici quelques-uns des spécificateurs de format :

%% Le caractère '%'
%d Jour du mois, numérique (00..31)
%m Mois de l'année, numérique (01..12)
%Y Année, sur 4 chiffres (YYYY)
%H Heures, sur 24 heures (00..23)
%i Minutes (00..59)
%s Secondes (00..59)
%T Heure complète (hh:mm:ss)
  ...



Pour la liste complète, voir la documentation MySQL

1.4. Conversion d'une chaîne en date

Pour créer une date/heure à partir d'une chaine, il suffit généralement de mettre la chaîne dans l'un des format suivants :

  • Pour les dates : "YYYY-MM-DD""YYYYMMDD""YY-MM-DD""YYMMDD"

  • Pour les heures : "hh:mm:ss""hhmmss"

  • Pour les date-heure : "YYYY-MM-DD hh:mm:ss""YY-MM-DD hh:mm:ss""YYYYMMDDhhmmss""YYMMDDhhmmss"

A noter aussi que vous pouvez spécifier tout type de séparateur. Ainsi, cette chaîne est tout à fait correcte : "2006.01.14 15%45%02" .

Si l'évaluation échoue (par exemple si la chaîne n'a aucun sens en tant que date), la date/heure résultante sera: "0000-00-00 00:00:00".



Pour une évaluation plus complexe, il faut avoir recours à la fonction STR_TO_DATE(), disponible uniquement à partir de la version 4.1.1 :

Nom Description Restrictions
de version
STR_TO_DATE() STR_TO_DATE(chaine, format) est la fonction inverse de DATE_FORMAT.

Evalue une chaîne de caractère et la convertit en date/heure en se basant sur le format spécifié. (voir la fonction DATE_FORMAT() pour la description du format).
4.1.1 +

1.5. Les UNIX_TIMESTAMP

Les "timestamp" UNIX représentent le nombre de secondes depuis l'epoch, c'est à dire depuis le 1er Janvier 1970 à minuit.

L'utilisation d'une telle représentation peut être utile pour certaines manipulations de dates, comme nous le verrons plus tard.

Voici les différentes fonctions utilisant les "timestamp" UNIX:

Nom Description Restrictions
de version
UNIX_TIMESTAMP() UNIX_TIMESTAMP()

Retourne l'heure courante, sous forme de timestamp UNIX.

 
  UNIX_TIMESTAMP(date)

Retourne l'heure passée en paramètre, sous forme de timestamp UNIX.

 
FROM_UNIXTIME() FROM_UNIXTIME(unix_timestamp)

Convertit le timestamp passé en paramètre en date.

 
  FROM_UNIXTIME(unix_timestamp, format)

Convertit le timestamp passé en paramètre en date, dans le format spécifié par format.

 

1.6. Ajout/soustraction d'intervalles de temps

Pour l'ajout/soustraction sur des date/heures, on dispose de plusieurs fonctions :

Nom Description Restrictions
de version
DATE_ADD() DATE_ADD(date, INTERVAL nb uniteDeTemps)

Ajoute nb unités de temps à la date passée en paramètre.

Les différentes valeurs d'uniteDeTemps sont données dans le tableau suivant.

Synonyme: ADDDATE(date, INTERVAL nb uniteDeTemps).

 
DATE_SUB()
DATE_SUB() se comporte comme DATE_ADD, mais pour la soustraction.

Synonyme: SUBDATE(date, INTERVAL nb uniteDeTemps).

 
ADDTIME() ADDTIME(date, expr) ajoute l'expression expr à la date/heure passée en paramètre.

Exemple:
SELECT ADDTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002');
-> '1998-01-02 01:01:01.000001'

4.1.1 +
(rétro)
SUBTIME()

SUBTIME() se comporte comme ADDTIME, mais pour la soustraction.


4.1.1 +
(rétro)



Et voici les différentes valeurs que peut prendre uniteDeTemps dans DATE_ADD() et DATE_SUB() :

Unité de temps Exemple Restrictions
de version
MICROSECOND 125 MICROSECOND 4.1.1 +
SECOND 20 SECOND  
MINUTE 30 MINUTE  
HOUR 2 HOUR  
DAY 3 DAY  
WEEK 3 WEEK 5.0.0 +
MONTH 2 MONTH  
QUARTER 3 QUARTER 5.0.0 +
YEAR 2 YEAR  
SECOND_MICROSECOND 20.125 SECOND_MICROSECOND 4.1.1 +
MINUTE_MICROSECOND 30:20.125 MINUTE_MICROSECOND 4.1.1 +
MINUTE_SECOND 30:20 MINUTE_SECOND 4.1.1 +
HOUR_MICROSECOND 2:30:20.125 HOUR_MICROSECOND 4.1.1 +
HOUR_SECOND 2:30:20 HOUR_SECOND 4.1.1 +
HOUR_MINUTE 2:30 HOUR_MINUTE  
DAY_MICROSECOND 3 2:30:20.125 DAY_MICROSECOND 4.1.1 +
DAY_SECOND 3 2:30:20 DAY_SECOND  
DAY_MINUTE 3 2:30 DAY_MINUTE  
DAY_HOUR 3 2 DAY_HOUR  
YEAR_MONTH 1-6 YEAR_MONTH  

1.7. Différences entre 2 dates/heures

Pour connaitre l'intervalle de temps entre 2 dates/heures, on dispose de fonctions telles que DATEDIFF() ou TIMEDIFF().

Nom Description Restrictions
de version
DATEDIFF() DATEDIFF(expr,expr2)

Retourne le nombre de jours entre la date de début expr et la date de fin expr2. expr et expr2 sont des expressions de type DATE ou DATETIME. Seule la partie DATE est utilisée dans le calcul. Le résultat peut être négatif.

SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
4.1.1 +
(rétro)
TIMEDIFF() TIMEDIFF(expr,expr2)

Retourne la durée entre l'heure de début expr et l'heure de fin expr2. expr et expr2 sont des expressions de type TIME ou DATETIME, et doivent être de même type

SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
4.1.1 +
(rétro)

1.8. Comparaison de dates/heures

Il existe plusieurs manières d'effectuer une comparaison entre 2 dates/heures :

  • Avec les opérateurs de comparaison ==, !=, <, <=, >, >= : SELECT * FROM produit WHERE dateLimite < CURDATE() ;
  • Pour les intervalles de temps, à l'aide de l'opérateur BETWEEN : SELECT * FROM produit WHERE dateLimite BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY) ;
  • A l'aide de n'importe quelle fonction sur les dates/heures : SELECT * FROM personne WHERE DATE_FORMAT(dateNaissance, "%d-%m") = DATE_FORMAT(CURDATE(), "%d-%m")




Cette section constitue un récapitulatif des fonctions de date/heures les plus couramment utilisées en MySQL.

Dans la section suivante, nous étudierons de nombreux problèmes récurrents
ayant recours à des manipulations de dates.





précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2006 Pierre Caboche. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.