Concevez votre site web avec PHP et MySQL

- sortie console -

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.

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.