[Plan du site] Vous êtes ici --- > Le Site du Zéro > Les tutoriels > Non-Officiels > Site Web > PHP > Base de données > MySQL et les données temporelles > Lecture du tutoriel

MySQL et les données temporelles

Vous vous apprêtez à lire un tutoriel rédigé par un membre de ce site. Malgré tout le soin que ce membre a pu apporter au tutoriel, nous ne pouvons pas garantir que les informations contenues sur cette page sont exactes à 100%. Merci de garder cela en tête lorsque vous lirez cette page ;o)

Bonjour à tous !

Ce tutoriel vous explique comment enregistrer correctement vos données temporelles dans votre Base De Données et propose plusieurs fonctions pour les exploiter.

Bonne lecture !

Sommaire du tutoriel :

Enregistrer les données
Fonctions de dates et d'heures
Exercices
Optimisation du script de news
Q.C.M.

Enregistrer les données

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".
Code : SQL

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

Enregistrement

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

1	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".
Code : SQL

1	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".
Code : SQL

1	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".
Code : SQL

1	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".
Code : SQL

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

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

Fonctions de dates et d'heures

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

Code : SQL

1 2	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 (la 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

Code : SQL

1 2	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

Code : SQL

1 2	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

Code : SQL

1 2	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

Code : SQL

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

[Exemple de la documentation]

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

MAKEDATE(année, jourdelanne)

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

Code : SQL

1 2	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

Code : SQL

1 2	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

Code : SQL

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 type type à la date donnée.
Les arguments sont les mêmes que pour la fonction DATE_ADD().

Exemple

Code : SQL

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 ne faut surtout pas faire les accords du pluriel !
Exemple : INTERVAL 5 DAYS => INTERVAL 5 DAY

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

Exercices

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
Code : SQL

1 2	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
Code : SQL

1 2	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
Code : SQL

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

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

Correction

Code : SQL

1	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
Code : SQL

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

À vous de jouer !

Correction

Code : SQL

1	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 elle 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.

Optimisation du script de news

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 :
Code : SQL

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'
);

J'ai nommé le champ qui contient la date temps mais vous pouvez lui donner le nom que vous désirez.
Toutefois, ne le nommez pas date pour éviter toute confusion avec le type de champ DATE.

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() :
Code : PHP

1 2	// 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 :
Code : PHP

<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

Code : PHP

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

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

Code : PHP

$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

Code : PHP

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

Vous pouvez très bien utiliser d'autres méthodes pour formater vos dates.
N'hésitez pas à consulter les différentes possibilités présentées sur le site Expreg.com ou à créer les vôtres !

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

<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é !

Q.C.M.

Si vous désirez stocker dans votre BDD une date complète correspondant au moment où le visiteur laisse un message dans votre livre d'or, quel type de champ choisissez-vous ?

VARCHAR (19) au format 05/01/2007 15:30:00.
2 champs : un VARCHAR(10) pour la date et un VARCHAR(8) pour l'heure.
DATETIME au format 2007-01-05 15:30:00.
Vous ne faites rien ... Trop compliquée, cette histoire !
INT(20) pour stocker le timestamp récupéré grâce à la fonction php time().

Laquelle de ces fonctions ne retourne pas la date et l'heure courante au format "AAAA-MM-JJ HH:MM:SS" ?

À votre avis, que va retourner la fonction ci-dessous ?
Code : SQL

1	SELECT DATE('2006-10-25 12:45:00')

Statistiques de réponses au QCM

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

	Auteur : Machin Note : 17 / 20 (7 votes) Visualisations : 21 437 Plus d'informations Créé : le 11/01/2007 à 13:06:08 Modifié : le 22/08/2008 à 16:07:14 Avancement : 90% Licence : Copie non autorisée

Tutoriels

Communauté

Mon compte

Recherche

Livre d'or

Publicité

MySQL et les données temporelles

Enregistrer les données

La date

Type de champ

Enregistrement

L'heure

Type de champ

Enregistrement

La date et l'heure

Type de champ

Enregistrement

Fonctions de dates et d'heures

DATEDIFF() : différence entre deux dates

DATEDIFF(date1, date2)

Exemple

DATE_FORMAT() et son contraire, STR_TO_DATE()

DATE_FORMAT(date, format)

Exemple

STR_TO_DATE(date, format)

Exemple

TIME_TO_SEC() : convertir une heure en secondes

TIME_TO_SEC(heure)

Exemple

UNIX_TIMESTAMP() : convertir une date en timestamp

UNIX_TIMESTAMP(date)

Exemple

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

MAKEDATE(année, jourdelanne)

Exemple

MAKETIME(heure, minutes, secondes)

Exemple

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

DATE_ADD(date, INTERVAL expression type)

Exemple

DATE_SUB(date, INTERVAL expression type)

Exemple

Exercices

Exercice 1

Correction

Exercice 2

Correction

Explications

Optimisation du script de news

Correction

Pour formater très simplement une date seule

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

Pour formater une date complète

Q.C.M.