• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

course.header.alt.is_certifying

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

J'ai tout compris !

Mis à jour le 22/05/2019

Formatez une donnée temporelle

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

Lorsque l'on tombe sur quelqu'un qui a fait le (mauvais) choix de stocker ses dates sous forme de chaînes de caractères, et qu'on lui demande les raisons de son choix, celle qui revient le plus souvent est qu'il ne veut pas afficher ses dates sous la forme 'AAAA-MM-JJ'. Donc il les stocke sous forme de CHAR  ou VARCHAR'JJ/MM/AAAA', par exemple, ou n'importe quel format de son choix.
Malheureusement, en faisant cela, il se prive des nombreux avantages des formats temporels SQL (en particulier toutes les fonctions que nous voyons dans cette partie), et cela pour rien, car SQL dispose de puissantes fonctions permettant de formater une donnée temporelle.
C'est donc ce que nous allons voir dans ce chapitre.

Extraire une information précise

Commençons en douceur avec des fonctions permettant d'extraire une information d'une donnée temporelle. Par exemple, le jour de la semaine, le nom du mois, l'année, etc.

Informations sur la date

Extraire la partie DATE

La fonction DATE(datetime)  permet d'extraire la partie DATE  d'une donnée de type DATETIME  (ou DATE, mais c'est moins utile…).

SELECT nom, date_naissance, 
        DATE(date_naissance) AS uniquementDate
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

uniquementDate

Safran

2007-03-04 19:36:00

2007-03-04

Gingko

2008-02-20 02:50:00

2008-02-20

Bavard

2009-03-26 08:28:00

2009-03-26

Parlotte

2009-03-26 07:55:00

2009-03-26

Le jour

Les fonctions suivantes donnent des informations sur le jour :

  • DAY(date)  ou DAYOFMONTH(date)  : donne le jour du mois (sous forme de nombre entier de 1 à 31).

  • DAYOFWEEK(date)  : donne l'index du jour de la semaine (nombre de 1 à 7 avec 1 = dimanche, 2 = lundi… 7 = samedi).

  • WEEKDAY(date)  : donne aussi l'index du jour de la semaine, de manière un peu différente (nombre de 0 à 6 avec 0 = lundi, 1 = mardi… 6 = dimanche).

  • DAYNAME(date)  : donne le nom du jour de la semaine.

  • DAYOFYEAR(date)  : retourne le numéro du jour par rapport à l'année (de 1 à 366 donc).

Exemples :

SELECT nom, DATE(date_naissance) AS date_naiss, 
        DAY(date_naissance) AS jour, 
        DAYOFMONTH(date_naissance) AS jour, 
        DAYOFWEEK(date_naissance) AS jour_sem,
        WEEKDAY(date_naissance) AS jour_sem2,
        DAYNAME(date_naissance) AS nom_jour, 
        DAYOFYEAR(date_naissance) AS jour_annee
FROM Animal
WHERE espece_id = 4;

nom

date_naiss

jour

jour

jour_sem

jour_sem2

nom_jour

jour_annee

Safran

2007-03-04

4

4

1

6

Sunday

63

Gingko

2008-02-20

20

20

4

2

Wednesday

51

Bavard

2009-03-26

26

26

5

3

Thursday

85

Parlotte

2009-03-26

26

26

5

3

Thursday

85

Tout cela fonctionne très bien, mais ce serait encore mieux si l'on pouvait avoir le nom des jours en français plutôt qu'en anglais. Aucun problème, il suffit de le demander, en exécutant la requête suivante :

SET lc_time_names = 'fr_FR';

Et voilà le travail :

SELECT nom, date_naissance, 
        DAYNAME(date_naissance) AS jour_semaine 
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

jour_semaine

Safran

2007-03-04 19:36:00

dimanche

Gingko

2008-02-20 02:50:00

mercredi

Bavard

2009-03-26 08:28:00

jeudi

Parlotte

2009-03-26 07:55:00

jeudi

La semaine

À partir d'une date, il est possible de calculer la semaine à laquelle elle correspond. S'agit-il de la première semaine de l'année ? De la quinzième ? Ceci peut être obtenu grâce à trois fonctions : WEEK(date)WEEKOFYEAR(date)  et YEARWEEK(date).

  • WEEK(date)  : donne uniquement le numéro de la semaine (un nombre entre 0 et 53, puisque 7 x 52 = 364, donc en un an, il y a 52 semaines et 1 ou 2 jours d'une 53e semaine).

  • WEEKOFYEAR(date)  : donne uniquement le numéro de la semaine (un nombre entre 1 et 53).

  • YEARWEEK(date)  : donne également l'année.

SELECT nom, date_naissance, WEEK(date_naissance) AS semaine, WEEKOFYEAR(date_naissance) AS semaine2, YEARWEEK(date_naissance) AS semaine_annee
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

semaine

semaine2

semaine_annee

Safran

2007-03-04 19:36:00

9

9

200709

Gingko

2008-02-20 02:50:00

7

8

200807

Bavard

2009-03-26 08:28:00

12

13

200912

Parlotte

2009-03-26 07:55:00

12

13

200912

WEEK()  et YEARWEEK()  peuvent également accepter un deuxième argument, qui sert à spécifier si la semaine doit commencer le lundi ou le dimanche, et ce que l'on considère comme la première semaine de l'année. Selon l'option utilisée par WEEK(), le résultat de cette fonction peut donc différer de celui de WEEKOFYEAR(). Si ces options vous intéressent, je vous invite à aller vous renseigner dans la documentation officielle.

Le mois

Pour le mois, il existe deux fonctions : MONTH(date)  qui donne le numéro du mois (nombre de 1 à 12) et MONTHNAME(date)  qui donne le nom du mois.

SELECT nom, date_naissance, MONTH(date_naissance) AS numero_mois, MONTHNAME(date_naissance) AS nom_mois
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

numero_mois

nom_mois

Safran

2007-03-04 19:36:00

3

mars

Gingko

2008-02-20 02:50:00

2

février

Bavard

2009-03-26 08:28:00

3

mars

Parlotte

2009-03-26 07:55:00

3

mars

L'année

Enfin, la fonction YEAR(date)  extrait l'année.

SELECT nom, date_naissance, YEAR(date_naissance)
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

YEAR(date_naissance)

Safran

2007-03-04 19:36:00

2007

Gingko

2008-02-20 02:50:00

2008

Bavard

2009-03-26 08:28:00

2009

Parlotte

2009-03-26 07:55:00

2009

Informations sur l'heure

En ce qui concerne l'heure, voici quatre fonctions intéressantes (et faciles à retenir) qui s'appliquent à une donnée de type DATETIME  ou TIME  :

  • TIME(datetime)  : qui extrait l'heure complète (le TIME).

  • HOUR(heure)  : qui extrait l'heure.

  • MINUTE(heure)  : qui extrait les minutes.

  • SECOND(heure)  : qui extrait les secondes.

SELECT nom, date_naissance, 
       TIME(date_naissance) AS time_complet, 
       HOUR(date_naissance) AS heure, 
       MINUTE(date_naissance) AS minutes, 
       SECOND(date_naissance) AS secondes
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

time_complet

heure

minutes

secondes

Safran

2007-03-04 19:36:00

19:36:00

19

36

0

Gingko

2008-02-20 02:50:00

02:50:00

2

50

0

Bavard

2009-03-26 08:28:00

08:28:00

8

28

0

Parlotte

2009-03-26 07:55:00

07:55:00

7

55

0

Formater une date facilement

Avec les fonctions que nous venons de voir, vous êtes maintenant capable d'afficher une date dans un joli format, par exemple "le lundi 8 novembre 1987".

SELECT nom, date_naissance, CONCAT_WS(' ', 'le', DAYNAME(date_naissance), DAY(date_naissance), MONTHNAME(date_naissance), YEAR(date_naissance)) AS jolie_date
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

jolie_date

Safran

2007-03-04 19:36:00

le dimanche 4 mars 2007

Gingko

2008-02-20 02:50:00

le mercredi 20 février 2008

Bavard

2009-03-26 08:28:00

le jeudi 26 mars 2009

Parlotte

2009-03-26 07:55:00

le jeudi 26 mars 2009

Cependant, il faut bien avouer que c'est un peu long à écrire. Heureusement, il existe une fonction qui va nous permettre de faire la même chose, en bien plus court : DATE_FORMAT(date, format).

Cette fonction DATE_FORMAT()  a donc deux paramètres :

  • date : la date à formater (DATETIME  ou DATETIME) ;

  • format : le format voulu.

Format

Le format à utiliser doit être donné sous forme de chaîne de caractères. Cette chaîne peut contenir un ou plusieurs spécificateurs dont les plus courants sont listés dans le tableau suivant.

Spécificateur

Description

%d

Jour du mois (nombre à deux chiffres, de 00 à 31)

%e

Jour du mois (nombre à un ou deux chiffres, de 0 à 31)

%D

Jour du mois, avec suffixe (1rst, 2nd,…, 31th) en anglais

%w

Numéro du jour de la semaine (dimanche = 0… samedi = 6)

%W

Nom du jour de la semaine

%a

Nom du jour de la semaine en abrégé

%m

Mois (nombre de deux chiffres, de 00 à 12)

%c

Mois (nombre de un ou deux chiffres, de 0 à 12)

%M

Nom du mois

%b

Nom du mois en abrégé

%y

Année, sur deux chiffres

%Y

Année, sur quatre chiffres

%r

Heure complète, format 12h (hh:mm:ss AM/PM)

%T

Heure complète, format 24h (hh:mm:ss)

%h

Heure sur deux chiffres et sur 12 heures (de 00 à 12)

%H

Heure sur deux chiffres et sur 24 heures (de 00 à 23)

%l

Heure sur un ou deux chiffres et sur 12 heures (de 0 à 12)

%k

Heure sur un ou deux chiffres et sur 24 heures (de 0 à 23)

%i

Minutes (de 00 à 59)

%s ou %S

Secondes (de 00 à 59)

%p

AM/PM

Tous les caractères ne faisant pas partie d'un spécificateur sont simplement recopiés tels quels.

Exemples

Même résultat que précédemment…

… Avec une requête bien plus courte :

SELECT nom, date_naissance, DATE_FORMAT(date_naissance, 'le %W %e %M %Y') AS jolie_date
FROM Animal
WHERE espece_id = 4;

nom

date_naissance

jolie_date

Safran

2007-03-04 19:36:00

le dimanche 4 mars 2007

Gingko

2008-02-20 02:50:00

le mercredi 20 février 2008

Bavard

2009-03-26 08:28:00

le jeudi 26 mars 2009

Parlotte

2009-03-26 07:55:00

le jeudi 26 mars 2009

Autres exemples
SELECT DATE_FORMAT(NOW(), 'Nous sommes aujourd''hui le %d %M de l''année %Y. Il est actuellement %l heures et %i minutes.') AS Top_date_longue;

SELECT DATE_FORMAT(NOW(), '%d %b. %y - %r') AS Top_date_courte;

Top_date_longue

Nous sommes aujourd'hui le 27 octobre de l'année 2011. Il est actuellement 3 heures et 17 minutes.

Top_date_courte

27 oct. 11 - 03:34:40 PM

Fonction supplémentaire pour l'heure

DATE_FORMAT()  peut s'utiliser sur des données de type DATETIME  ou DATETIME. Mais il existe également une fonction TIME_FORMAT(heure, format)qui ne sert qu'à formater les heures (et ne doit donc pas s'utiliser sur une DATE). Elle s'utilise exactement de la même manière, simplement, il faut y utiliser des spécificateurs ayant du sens pour une donnée TIME, sinon NULLou 0  est renvoyé.
Si un mauvais format de TIME  ou DATETIME  est donné à TIME_FORMAT()  (par exemple, si on lui donne une DATE), MySQL va tenter d'interpréter la donnée et renverra bien un résultat, mais celui-ci n'aura peut-être pas beaucoup de sens (pour vous du moins).

Exemples :

-- Sur une DATETIME
SELECT TIME_FORMAT(NOW(), '%r') AS sur_datetime, 
       TIME_FORMAT(CURTIME(), '%r') AS sur_time, 
       TIME_FORMAT(NOW(), '%M %r') AS mauvais_specificateur, 
       TIME_FORMAT(CURDATE(), '%r') AS sur_date;

sur_datetime

sur_time

mauvais_specificateur

sur_date

10:58:47 AM

10:58:47 AM

NULL

12:20:12 AM

L'application de TIME_FORMAT()  sur CURDATE()  a renvoyé un avertissement :

Level

Code

Message

Warning

1292

Truncated incorrect time value: '2012-04-30'

Formats standard

Il existe un certain nombre de formats de date et d'heure standard prédéfinis, que l'on peut utiliser dans la fonction DATE_FORMAT(). Pour obtenir ces formats, il faut appeler la fonction GET_FORMAT(type, standard).

Le paramètre type doit être choisi entre les trois types de données : DATETIME  et DATETIME.

Il existe cinq formats standard :

  • 'EUR'

  • 'USA'

  • 'ISO'

  • 'JIS'

  • 'INTERNAL'

Voici un tableau reprenant les différentes possibilités :

Fonction

Format

Exemple

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

10.30.1988

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

1988-10-30

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

1988-10-30

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

30.10.1988

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

19881031

GET_FORMAT(DATETIME,'USA')

'%Y-%m-%d %H.%i.%s'

1988-10-30 13.44.33

GET_FORMAT(DATETIME,'JIS')

'%Y-%m-%d %H:%i:%s'

1988-10-30 13:44:33

GET_FORMAT(DATETIME,'ISO')

'%Y-%m-%d %H:%i:%s'

1988-10-30 13:44:33

GET_FORMAT(DATETIME,'EUR')

'%Y-%m-%d %H.%i.%s'

1988-10-30 13.44.33

GET_FORMAT(DATETIME,'INTERNAL')

'%Y%m%d%H%i%s'

19881030134433

GET_FORMAT(TIME,'USA')

'%h:%i:%s %p'

1:44:33 PM

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

13:44:33

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

13:44:33

GET_FORMAT(TIME,'EUR')

'%H.%i.%S'

13.44.33

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

134433

Exemples :

SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATE, 'EUR')) AS date_eur,
       DATE_FORMAT(NOW(), GET_FORMAT(TIME, 'JIS')) AS heure_jis,
       DATE_FORMAT(NOW(), GET_FORMAT(DATETIME, 'USA')) AS date_heure_usa;

date_eur

heure_jis

date_heure_usa

29.04.2012

11:20:55

2012-04-29 11.20.55

Créer une date à partir d'une chaîne de caractères

Voici une dernière fonction ayant trait au format des dates : STR_TO_DATE(date, format). Cette fonction est l'exact contraire de DATE_FORMAT()  : elle prend une chaîne de caractères représentant une date suivant le format donné, et renvoie la DATETIME  correspondante.

Exemples :

SELECT STR_TO_DATE('03/04/2011 à 09h17', '%d/%m/%Y à %Hh%i') AS StrDate,
       STR_TO_DATE('15blabla', '%Hblabla') StrTime;

StrDate

StrTime

2011-04-03 09:17:00

15:00:00

Il est bien sûr possible d'utiliser GET_FORMAT()  aussi avec STR_TO_DATE().

SELECT STR_TO_DATE('11.21.2011', GET_FORMAT(DATE, 'USA')) AS date_usa,
       STR_TO_DATE('12.34.45', GET_FORMAT(TIME, 'EUR')) AS heure_eur,
       STR_TO_DATE('20111027133056', GET_FORMAT(TIMESTAMP, 'INTERNAL')) AS date_heure_int;

date_usa

heure_eur

date_heure_int

2011-11-21

12:34:45

2011-10-27 13:30:56

En résumé
  • De nombreuses fonctions permettent d'extraire un élément précis à partir d'une donnée temporelle : HOUR()  permet d'extraire l'heure, YEAR()  extrait l'année…

  • On peut formater une donnée temporelle facilement en utilisant DATE_FORMAT()  et TIME_FORMAT()  avec des spécificateurs : "%M" représente le nom du mois, "%i" les minutes…

  • Il existe cinq formats de date standard : EUR, ISO, USA, JIS et INTERNAL.

  • Pour que les noms des jours et des mois soient donnés en français, il faut exécuter cette commande : SET lc_time_names = 'fr_FR';

  • Lorsque l'on a une donnée temporelle dans un format particulier, on peut la transformer en DATETIME  ou DATETIME  avec STR_TO_FORMAT(), qui utilise les mêmes spécificateurs que DATE_FORMAT()  et TIME_FORMAT().

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