Lorsque nous avons construit nos tables, nous avons utilisé différents types de champs, notamment INT
(nombre entier), VARCHAR
(texte court) et TEXT
(texte long). Vous avez pu découvrir dans phpMyAdmin qu'il existait de nombreux autres types. La plupart ne sont que des variations de ces types, pour stocker par exemple de très petits ou de très grands nombres. La plupart du temps, vous n'aurez pas à vous soucier de tous ces types : INT
suffit amplement pour les nombres entiers, par exemple.
Les dates sont plus délicates à manier en SQL, et, pourtant, on en a très souvent besoin. Par exemple, dans le TP du minichat, on pourrait s'en servir pour stocker le jour et l'heure précise où chaque message a été posté. Il en va de même si vous construisez un système de forum ou de news pour votre site : vous aurez besoin d'enregistrer la date à chaque fois.
Nous ferons d'abord le tour des types de champs à connaître pour stocker des dates avec MySQL, et nous verrons comment les utiliser. Nous pourrons ensuite découvrir de nouvelles fonctions SQL dédiées aux dates.
Les champs de type date
Dans ce chapitre, je vous propose d'améliorer un peu le minichat que nous avons créé dans un précédent TP.
Saviez-vous qu'il était possible de modifier la structure d'une table après sa création ? On peut en effet y ajouter ou en supprimer des champs à tout moment. Ouvrez la table minichat
dans phpMyAdmin, onglet « Structure ». Cherchez le formulaire « Ajouter 1 colonne » et cliquez sur le bouton « Exécuter ».

Un formulaire apparaît, vous proposant de créer un nouveau champ. C'est l'occasion de passer en revue les différents types de champs qui permettent de stocker des dates.
Les différents types de dates
Voici les différents types de dates que peut stocker MySQL :
DATE
: stocke une date au format AAAA-MM-JJ (Année-Mois-Jour) ;TIME
: stocke un moment au format HH:MM:SS (Heures:Minutes:Secondes) ;DATETIME
: stocke la combinaison d'une date et d'un moment de la journée au format AAAA-MM-JJ HH:MM:SS. Ce type de champ est donc plus précis ;TIMESTAMP
: stocke le nombre de secondes passées depuis le 1er janvier 1970 à 00 h 00 min 00 s ;YEAR
: stocke une année, soit au format AA, soit au format AAAA.
Cela fait beaucoup de choix ! Dans la pratique, je vous invite à retenir surtout DATE
(AAAA-MM-JJ) quand le moment de la journée importe peu, et DATETIME
(AAAA-MM-JJ HH:MM:SS) quand vous avez besoin du jour et de l'heure précise à la seconde près.
Créez un champ nommé date
de type DATETIME
comme sur la figure suivante, en mettant comme valeur par défaut CURRENT_TIMESTAMP (ce qui veut dire "la date actuelle").

Utilisation des champs de date en SQL
Les champs de type date
s'utilisent comme des chaînes de caractères : il faut donc les entourer d'apostrophes. Vous devez écrire la date dans le format du champ.
Par exemple, pour un champ de type DATE
:
SELECT pseudo, message, date FROM minichat WHERE date = '2010-04-02'
… vous renverra la liste des messages postés le 02/04/2010 (2 avril 2010).
Si le champ est de type DATETIME
(comme c'est le cas pour notre nouveau minichat), il faut aussi indiquer précisément les heures, minutes et secondes :
SELECT pseudo, message, date FROM minichat WHERE date = '2010-04-02 15:28:22'
Cela vous renverra la liste des messages postés le 02/04/2010 à 15h28min22s.
Bon : je reconnais que c'est un peu précis, il est peu probable que beaucoup de messages aient été postés à ce moment exact.
En revanche, et c'est là que les champs de date deviennent réellement intéressants, vous pouvez utiliser d'autres opérateurs que le signe égal. Par exemple, on peut obtenir la liste de tous les messages postés après cette date :
SELECT pseudo, message, date FROM minichat WHERE date >= '2010-04-02 15:28:22'
Ou même la liste de tous les messages postés entre le 02/04/2010 et le 18/04/2010 :
SELECT pseudo, message, date FROM minichat WHERE date >= '2010-04-02 00:00:00' AND date <= '2010-04-18 00:00:00'
Si vous voulez insérer une entrée contenant une date, il suffit là encore de respecter le format de date de la base de données :
INSERT INTO minichat(pseudo, message, date) VALUES('Mateo', 'Message !', '2010-04-02 16:32:22')
Les fonctions de gestion des dates
Il existe de très nombreuses fonctions de manipulation des dates. Utilisées sur des champs de type DATE
ou DATETIME
, par exemple, elles permettent d'extraire très facilement toutes sortes d'informations utiles sur les dates, comme l'année, le numéro du jour du mois, le numéro du jour dans l'année, etc. Il est aussi possible d'effectuer des opérations sur les dates.
Il est impossible de lister toutes les fonctions de gestion des dates, mais vous trouverez la liste complète de celles-ci dans la documentation de MySQL au besoin. Cette introduction aux dates devrait être suffisante pour que vous puissiez vous débrouiller tout seul par la suite.
NOW()
: obtenir la date et l'heure actuelles
C'est probablement une des fonctions que vous utiliserez le plus souvent. Lorsque vous insérerez un nouveau message dans la base, vous souhaiterez enregistrer la date actuelle les 99 % du temps. Pour cela, rien de plus simple avec la fonction NOW()
:
INSERT INTO minichat(pseudo, message, date) VALUES('Mateo', 'Message !', NOW())
La date sera alors automatiquement remplacée par la date et l'heure actuelles au format AAAA-MM-JJ HH:MM:SS.
Notez qu'il existe aussi les fonctions CURDATE()
et CURTIME()
qui retournent respectivement uniquement la date (AAAA-MM-JJ) et l'heure (HH:MM:SS).
DAY()
, MONTH()
, YEAR()
: extraire le jour, le mois ou l'année
Extraire des informations d'une date ? C'est facile ! Voici un exemple d'utilisation :
SELECT pseudo, message, DAY(date) AS jour FROM minichat
On récupèrera trois champs : le pseudo, le message et le numéro du jour où il a été posté.
HOUR()
, MINUTE()
, SECOND()
: extraire les heures, minutes, secondes
De la même façon, avec ces fonctions il est possible d'extraire les heures, minutes et secondes d'un champ de type DATETIME
ou TIME
.
SELECT pseudo, message, HOUR(date) AS heure FROM minichat
DATE_FORMAT
: formater une date
Avec les fonctions que l'on vient de découvrir à l'instant, on pourrait extraire tous les éléments de la date, comme ceci :
SELECT pseudo, message, DAY(date) AS jour, MONTH(date) AS mois, YEAR(date) AS annee, HOUR(date) AS heure, MINUTE(date) AS minute, SECOND(date) AS seconde FROM minichat
On pourrait ensuite afficher la date en PHP dans l'ordre que l'on souhaite, à l'aide du découpage en champs que l'on vient de faire :
<?php
echo $donnees['jour'] . '/' . $donnees['mois'] . '/' . $donnees['annee'] . '...';
?>
C'est cependant un peu compliqué, et il y a plus simple. La fonction DATE_FORMAT
vous permet d'adapter directement la date au format que vous préférez. Il faut dire que le format par défaut de MySQL (AAAA-MM-JJ HH:MM:SS) n'est pas très courant en France.
Voici comment on pourrait l'utiliser :
SELECT pseudo, message, DATE_FORMAT(date, '%d/%m/%Y %Hh%imin%ss') AS date FROM minichat
Ainsi, on récupèrerait les dates avec un champ nommé date
sous la forme 11/03/2010 15h47min49s.
Comment ça marche, ce bazar ?
Les symboles %d
, %m
, %Y
, etc., sont remplacés par le jour, le mois, l'année, etc. Les autres symboles et lettres sont affichés tels quels.
Il existe beaucoup d'autres symboles pour extraire par exemple le nom du jour (la plupart du temps en anglais malheureusement, comme « Saturday »), le numéro du jour dans l'année, etc. La liste des symboles disponibles est dans la doc' à la section DATE_FORMAT
.
DATE_ADD
et DATE_SUB
: ajouter ou soustraire des dates
Il est possible d'ajouter ou de soustraire des heures, minutes, secondes, jours, mois ou années à une date. Il faut envoyer deux paramètres à la fonction : la date sur laquelle travailler et le nombre à ajouter, ainsi que son type.
Par exemple, supposons que l'on souhaite afficher une date d'expiration du message. Celle-ci correspond à « la date où a été posté le message + 15 jours ». Voici comment écrire la requête :
SELECT pseudo, message, DATE_ADD(date, INTERVAL 15 DAY) AS date_expiration FROM minichat
Le champ date_expiration
correspond à « la date de l'entrée + 15 jours ». Le mot-clé INTERVAL
ne doit pas être changé ; en revanche, vous pouvez remplacer DAY
par MONTH
,YEAR
, HOUR
, MINUTE
, SECOND
, etc. Par conséquent, si vous souhaitez indiquer que les messages expirent dans deux mois :
SELECT pseudo, message, DATE_ADD(date, INTERVAL 2 MONTH) AS date_expiration FROM minichat
En résumé
MySQL propose plusieurs types de champs pour stocker des dates.
Les deux types les plus couramment utilisés sont :
DATE
: stocke une date au format AAAA-MM-JJ ;DATETIME
: stocke une date et une heure au format AAAA-MM-JJ HH:MM:SS.
On peut trier et filtrer des champs contenant des dates comme s'il s'agissait de nombres.
Il existe de nombreuses fonctions SQL dédiées au traitement des dates. La plus connue est
NOW()
qui renvoie la date et l'heure actuelles.