Tutoriel : MySQL et les données temporelles

Voici les bonnes solutions pour stocker vos données temporelles (dates et heures) dans votre Base De Données : les types de champ DATE pour la date, TIME pour l'heure et DATETIME pour les deux, assemblées.

La date

Type de champ

Il faut utiliser le type DATE pour stocker une date au format "AAAA-MM-JJ".

votre_date date NOT NULL DEFAULT '0000-00-00'

Enregistrement

Les fonctions CURDATE() et CURRENT_DATE() retournent la date courante au format voulu, soit "AAAA-MM-JJ".

INSERT INTO votre_table (votre_date) VALUES (CURDATE());

L'heure

Type de champ

Il faut utiliser le type TIME pour stocker une heure au format "HH:MM:SS".

votre_heure time NOT NULL DEFAULT '00:00:00'

Enregistrement

Les fonctions CURTIME() et CURRENT_TIME() retournent l'heure courante au format voulu, soit "HH:MM:SS".

INSERT INTO votre_table (votre_heure) VALUES (CURTIME());

La date et l'heure

Type de champ

Si vous voulez enregistrer une date complète, c'est-à-dire comprenant aussi l'heure, il faut utiliser le type de champ DATETIME. Le format de votre date est : "AAAA-MM-JJ HH:MM:SS".

date_complete datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

Enregistrement

La fonction NOW() ainsi que ses innombrables synonymes comme CURRENT_TIMESTAMP() retournent la date complète au format "AAAA-MM-JJ HH:MM:SS".

INSERT INTO votre_table (date_complete) VALUES (NOW());

Maintenant, vous savez stocker correctement vos données temporelles dans votre BDD.

Vous allez découvrir différentes fonctions de dates et d'heures méconnues et pourtant tellement pratiques !

DATEDIFF() : différence entre deux dates

DATEDIFF(date1, date2)

Cette fonction calcule le nombre de jours entre la date1 et la date2. Les dates sont de type DATE ou DATETIME (dans ce dernier cas, l'heure est ignorée).

Exemple

SELECT DATEDIFF('2007-01-02', '2007-01-08');
=> 6

DATE_FORMAT() et son contraire, STR_TO_DATE()

DATE_FORMAT(date, format)

Cette fonction transforme une date donnée au format indiqué.
La liste des formats est longue, voici les principaux :
- %d/%m/%Y => 01/12/2006 (le format français) ;
- %W %e %M => Monday 5 December (les noms sont en anglais) ;
- %X %V => 2007 51 (année et numéro de la semaine).
Retrouvez le reste des formats dans la documentation.

Exemple

SELECT DATE_FORMAT('2006-10-25 14:27:39', '%d/%m/%Y');
=> 25/10/2006

STR_TO_DATE(date, format)

Cette fonction est l'inverse de DATE_FORMAT(). Elle transforme la date donnée avec son format que vous devez indiquer en une date de type DATETIME (AAAA-MM-JJ).

Exemple

SELECT STR_TO_DATE('25/10/2006 14:27:39', '%d/%m/%Y %T');
=> 2006-10-25 14:27:39

Remarque : %T est l'heure au format HH:MM:SS.

TIME_TO_SEC() : convertir une heure en secondes

TIME_TO_SEC(heure)

Cette fonction convertit une heure donnée en secondes.

Exemple

SELECT TIME_TO_SEC('05:30:00')
=> 19800

UNIX_TIMESTAMP() : convertir une date en timestamp

UNIX_TIMESTAMP(date)

Cette fonction retourne le timestamp correspondant à une date donnée (type DATE ou DATETIME).

Exemple

SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
=> 875996580

[Exemple de la documentation]

MAKEDATE() et MAKETIME() : création de dates

MAKEDATE(année, jourdelannee)

Cette fonction crée une date au format DATE (AAAA-MM-JJ) à partir de deux éléments : l'année et le jour de l'année.

Exemple

SELECT MAKEDATE(2004,365);
=> 2004-12-31

MAKETIME(heure, minutes, secondes)

De la même manière, cette fonction crée une heure au format TIME (HH:MM:SS) à partir de 3 éléments : l'heure, les minutes et les secondes.

Exemple

SELECT MAKETIME(14,55,20);
=> 14:55:20

DATE_ADD() et DATE_SUB() : calculs arithmétiques sur les dates

DATE_ADD(date, INTERVAL expression type)

Cette fonction ajoute l'intervalle de temps expression du type type à la date donnée.
La date peut être au format DATE ou au format DATETIME.
L'argument type accepte les valeurs suivantes :
- HOUR (heure) ;
- DAY (jour) ;
- WEEK (semaine) ;
- MONTH (mois) ;
- YEAR (année).
Retrouvez la liste complète des types possibles dans la documentation.

Exemple

SELECT DATE_ADD('2005-05-15 12:00:00', INTERVAL 5 DAY);
=> 2005-05-20 12:00:00
SELECT DATE_ADD('2000-01-01', INTERVAL 7 YEAR);
=> 2007-01-01
SELECT DATE_ADD('2007-06-15 12:00:00', INTERVAL '2:30:00' HOUR_SECOND);
=> 2007-06-15 14:30:00

DATE_SUB(date, INTERVAL expression type)

Cette fonction fait le contraire de DATE_ADD() : elle soustrait l'intervalle de temps expression du typetype à la date donnée.
Les arguments sont les mêmes que pour la fonction DATE_ADD().

Exemple

SELECT DATE_SUB('2004-01-05', INTERVAL 31 DAY);
=> 2003-12-05
SELECT DATE_SUB('2006-10-01', INTERVAL '6-5' YEAR_MONTH);
=> 2000-05-01

Il existe bien d'autres fonctions mais je vous présente seulement les principales, du moins à mes yeux.

Je vous propose de créer quelques requêtes SQL.

Exercice 1

Imaginons que vous souhaitez réaliser une requête sélectionnant le(s) pseudo(s) dans votre table membres à condition que l'heure d'inscription du membre (champ inscription) soit égale à 22, c'est-à-dire qu'il se soit inscrit à 22 heures.

Pour réaliser cette requête, il est nécessaire de connaître quelques nouvelles fonctions.

- HOUR(heure)
Cette fonction retourne le nombre d'heures uniquement d'une heure que vous donnez au format TIME ou DATETIME.
Exemple

SELECT HOUR('15:30:00');
=> 15

- MINUTE(heure)
Cette fonction retourne le nombre de minutes uniquement d'une heure que vous donnez au format TIME ou DATETIME.
Exemple

SELECT MINUTE('2007-01-05 10:25:30');
=> 25

- SECOND(heure)
Même principe pour cette fonction qui retourne quant à elle les secondes uniquement.
Exemple

SELECT SECOND('12:56:43');
=> 43

Vous avez maintenant tous les éléments nécessaires pour créer la requête : au travail !

Correction

SELECT pseudo FROM membres WHERE HOUR(inscription) = 22;

Exercice 2

Passons à une requête un peu plus utile : vous devez réaliser une requête permettant de sélectionner le(s) pseudo(s) dans votre table membres qui se sont inscrits (champ inscription) depuis plus de 100 jours, c'est-à-dire les membres ayant plus de 100 jours d'ancienneté.

Je dois pour cela vous présenter une fonction qui vous sera bien utile.

- TO_DAYS(date)
Cette fonction retourne le nombre de jours depuis la date 0 jusqu'à la date indiquée.
Exemple

TO_DAYS('1997-10-07');
=> 729669

À vous de jouer !

Correction

SELECT pseudo FROM membres WHERE TO_DAYS(NOW()) - TO_DAYS(inscription) > 100 ;

Explications

On calcule la différence entre le nombre de jours au moment de la requête et le nombre de jours au moment de l'inscription : on obtient ainsi le nombre de jours d'ancienneté du membre.

Ces requêtes ne sont pas réellement indispensables mais elles vous permettent d'appliquer concrètement les fonctions, et de parvenir à cette conclusion : ces fonctions concernant les dates et les heures sont à utiliser dans les éléments SELECT et WHERE.

Vous allez maintenant utiliser vos nouvelles connaissances pour coder un script de news. En réalité, je vous propose de vous baser sur ce script qui provient du cours PHP.
Je vous rappelle qu'il est très bien codé, hormis tout ce qui touche aux données temporelles.

Voilà ce que je vous propose : reprenez ce script et optimisez-le grâce aux connaissances en SQL que vous avez acquises.
Bonne chance !

Correction

Pour commencer, je vous propose de revoir structure de la table news.
Pour stocker la date à laquelle la news est postée, il faut utiliser un champ de type DATETIME.

Voilà le code pour créer la table news :

CREATE TABLE `news` (
id int(11) NOT NULL AUTO_INCREMENT,
titre varchar(255) NOT NULL,
contenu text NOT NULL,
temps datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
);

Il faut désormais enregistrer les dates au format voulu, soit "AAAA-MM-JJ HH-MM-SS".
Dans le fichier liste_news.php, il faut modifier la requête SQL pour enregistrer la date courante grâce à la fonction NOW() :

// Ce n'est pas une modification, on crée une nouvelle entrée dans la table
mysql_query("INSERT INTO news VALUES('', '" . $titre . "', '" . $contenu . "', NOW())");

Enfin, il faut modifier l'affichage des news.
En réalité, seule cette partie nécessite une amélioration :

<h3>
    <?php echo $donnees['titre']; ?>
    <em>le <?php echo date('d/m/Y à H\hi', $donnees['timestamp']); ?></em>
</h3>

Il faut formater la date au format français : "JJ-MM-AAAA HH:MM:SS".
Vous savez le faire grâce à la fonction DATE_FORMAT(), cependant il est préférable de formater les données du côté php : c'est bien plus rapide !

Il existe de nombreuses solutions pour formater une date, je vais vous en présenter quelques-unes.

Pour formater très simplement une date seule

$madate = explode('-', $madate); 
echo $madate[2].'/'.$madate[1].'/'.$madate[0];

Pour formater une date seule encore (source : Expreg.com)

$madate = "2003-06-19"; 
$motif = '`(\d{4})-(\d{1,2})-(\d{1,2})`'; 
$afficher = '$3-$2-$1'; 
echo preg_replace($motif,$afficher,$madate);

Pour formater une date complète

sscanf($madate, "%4s-%2s-%2s %2s:%2s", $annee, $mois, $jour, $heure, $minute);
echo $jour.'/'.$mois.'/'.$annee.' '.$heure.':'.$minute;

Revenons à nos moutons !
Voilà le code pour afficher la date de notre news :

<h3>
<?php 
// À placer n'importe où avant.
sscanf($donnees['temps'], "%4s-%2s-%2s %2s:%2s", $annee, $mois, $jour, $heure, $minute); ?>
    
    <?php echo $donnees['titre']; ?>
    <em>le <?php echo $jour.'/'.$mois.'/'.$annee.' à '.$heure.':'.$minute; ?></em>
</h3>

Maintenant, votre code est bien optimisé !

Vous pouvez retrouver d'autres fonctions qui peuvent éventuellement vous servir dans la documentation MySQL : accéder à la page.

Pour conclure, je tiens à faire quelques précisions au sujet des formats de dates :
- le format "AAAA-MM-JJ" n'est en aucun cas le format américain mais le format ISO 8601 (norme internationale) ;
- le format américain est "MM/JJ/AAAA" ;
- en savoir plus sur les différents formats de date dans le monde.

Merci d'avoir lu ce tutoriel en espérant qu'il vous ait apporté des connaissances supplémentaires.
Machin