Fonctions de date/heure en MySQL

Pierre Caboche

Cet article a pour but de présenter les fonctions de dates de MySQL ainsi que d'exposer des solutions à des problèmes récurrents faisant intervenir des dates. ♪

- Titre : Fonctions de date/heure en MySQL

- Auteur : Pierre Caboche

- Parution : 12 janvier 2006

- Mise à jour : 25 janvier 2008

- Licence : Copyright ® 2006 Pierre Caboche. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Introduction

- Références

- I. Fonctions de dates et heures

- I-A. Représentation des dates

- I-B. Date/Heure actuelle : fonctions CURDATE(), CURTIME(), NOW() et SYSDATE()

- I-C. Formatage d'une date : fonction DATE_FORMAT()

- I-D. Conversion d'une chaine en date

- I-E. Les UNIX_TIMESTAMP

- I-F. Ajout/soustraction d'intervalles de temps

- I-G. Différences entre deux dates/heures

- I-H. Comparaison de dates/heures

- I-I. Études de cas

- Cas considérés

- Réservation d'une chambre d'hôtel

- La requête

- Évènements les plus récents d'un historique

- Requête de base

- Raffinement 1 : les N derniers évènements de l'historique

- Raffinement 2 : prise en compte du matériel n'apparaissant pas dans l'historique

- Raffinement 3 : spécifications multicritères

- Statistiques sur une période donnée

- Prérequis

- La requête

- Dans la pratique

- Anniversaires

- La requête

- Horaires Glissants, Tranches Horaires

- La requête

- Temps total de réalisation d'une tâche

- La requête

- Mise à jour automatique des dates de modification d'enregistrement

- MySQL version 4.1.2+

- Rétrocompatibilité

- II. Rétrocompatibilité

- Pallier l'absence de ADDTIME()/SUBTIME()

- Pallier l'absence de DATEDIFF()/TIMEDIFF()

- Pallier l'absence de DATE()

- III. Bêtisier : les erreurs à ne pas faire

- Erreur d'ajout de temps

- Énoncé du problème

- Origine de l'erreur

Introduction

MySQL est un SGBD extrêmement jeune comparé aux autres produits du marché tels que Oracle ou SQL Server. Par conséquent, les différentes fonctionnalités du SQL standard sont implémentées au fil des versions. Cela est particulièrement vrai pour les fonctions de date et heures.

Dans cet article, nous présenterons les différentes fonctions de date et heures sous MySQL en prenant soin de préciser dans quelle version de MySQL elles sont apparues (section 1).

Nous exposerons également différents exemples d'utilisation des fonctions de dates/heures dans une étude de cas réels (section 2).

Enfin nous proposerons des solutions aux problèmes de rétrocompatibilité de certaines fonctions dates/heures (section 3).

Références

Pour bien comprendre les études de cas, vous devez être familier avec les jointures. Pour plus de renseignements, lisez cet article :

- Le SQL de A à Z : 3e partie - les jointures, par SQLPro

Documentation MySQL, selon les versions : 3.23, 4.0, 4.15.05.1

I - Fonctions de dates et heures

Cette section présente les fonctions de date/heures les plus couramment utilisées.

Pour plus de détails ou pour d'autres fonctions, veuillez vous référer à la documentation propre à votre version de MySQL :
3.23, 4.0, 4.15.05.1

Comme annoncé en introduction, les fonctions de dates disponibles dépendent de votre version de MySQL. Pour chaque fonction de date/heures, les restrictions liées à la version de MySQL sont précisées, avec éventuellement un lien vers la section Rétrocompatibilité lorsque des solutions alternatives existent.

I-A - Représentation des dates

À l'affichage, suivant le contexte, les dates et heures peuvent être représentées :

- sous forme de chaines, au format suivant :

- pour les dates: "YYYY-MM-DD",

- pour les heures: "hh:mm:ss",

- pour les dates-heures: "YYYY-MM-DD hh:mm:ss" ;

- sous forme d'entiers, de la forme :

- pour les dates : YYYYMMDD,

- pour les heures : hhmmss,

- pour les dates-heures : YYYYMMDDhhmmss.

Par défaut, les dates/heures sont représentées sous forme de chaines.

Représentation sous forme de chaine :

SELECT NOW();
-> '2006-01-14 15:43:02'

Représentation sous forme d'entier :

SELECT NOW() + 0;
-> 20060114154302

I-B - Date/Heure actuelle : fonctions CURDATE(), CURTIME(), NOW() et SYSDATE()

Voici les fonctions qui vont vous permettre de connaitre les date et heure courantes :

Nom

Description

Restrictions
de version

CURDATE()

CURDATE() retourne la date actuelle (ex. : "2006-01-14").

Lorsque CURDATE() est affectée à un champ de type DATETIME, l'heure 00:00:00 est automatiquement ajoutée (ex. : "2006-01-14 00:00:00"), ce qui correspond à la date du jour, à minuit.

CURTIME()

CURTIME() retourne l'heure actuelle (ex. : "15:41:32").

NOW()

NOW() retourne la date et l'heure actuelles (ex. : "2006-01-14 15:41:32").

SYSDATE()

SYSDATE() retourne la date et l'heure courante.

La différence avec NOW() est que SYSDATE() retourne l'heure à laquelle elle est effectivement appelée, alors que NOW() retourne l'heure de début de script.

I-C - Formatage d'une date : fonction DATE_FORMAT()

La fonction DATE_FORMAT permet le formatage d'une date dans le format désiré.

Sa syntaxe est la suivante : DATE_FORMAT(date, format)

Voici quelques-uns des spécificateurs de format :

%%

Le caractère '%'

%d

Jour du mois, numérique (00..31)

%m

Mois de l'année, numérique (01..12)

%Y

Année, sur 4 chiffres (YYYY)

%H

Heures, sur 24 heures (00..23)

%i

Minutes (00..59)

%s

Secondes (00..59)

%T

Heure complète (hh:mm:ss)

…

Pour la liste complète, voir la documentation MySQL.

I-D - Conversion d'une chaine en date

Pour créer une date/heure à partir d'une chaine, il suffit généralement de mettre la chaine dans l'un des formats suivants :

- pour les dates :

- "YYYY-MM-DD",

- "YYYYMMDD",

- "YY-MM-DD",

- "YYMMDD" ;

- pour les heures :

- "hh:mm:ss",

- "hhmmss" ;

- pour les dates-heures :

- "YYYY-MM-DD hh:mm:ss" ;

- "YY-MM-DD hh:mm:ss" ;

- "YYYYMMDDhhmmss" ;

- "YYMMDDhhmmss".

À noter aussi que vous pouvez spécifier tout type de séparateur. Ainsi, cette chaine est tout à fait correcte : "2006.01.14 15%45%02" .

Si l'évaluation échoue (par exemple si la chaine n'a aucun sens en tant que date), la date/heure résultante sera: "0000-00-00 00:00:00".

Pour une évaluation plus complexe, il faut avoir recours à la fonction STR_TO_DATE(), disponible uniquement à partir de la version 4.1.1 :

Nom

Description

Restrictions
de version

STR_TO_DATE()

STR_TO_DATE(chaine, format) est la fonction inverse de DATE_FORMAT.

Évalue une chaine de caractères et la convertit en date/heure en se basant sur le format spécifié. (voir la fonction DATE_FORMAT() pour la description du format).

4.1.1 +

I-E - Les UNIX_TIMESTAMP

Les "timestamp" UNIX représentent le nombre de secondes depuis l'epoch, c'est-à-dire depuis le 1er janvier 1970 à minuit.

L'utilisation d'une telle représentation peut être utile pour certaines manipulations de dates, comme nous le verrons plus tard.

Voici les différentes fonctions utilisant les "timestamp" UNIX :

Nom

Description

Restrictions
de version

UNIX_TIMESTAMP()

UNIX_TIMESTAMP()

Retourne l'heure courante, sous forme de timestamp UNIX.

UNIX_TIMESTAMP(date)

Retourne l'heure passée en paramètre, sous forme de timestamp UNIX.

FROM_UNIXTIME()

FROM_UNIXTIME(unix_timestamp)

Convertit le timestamp passé en paramètre en date.

FROM_UNIXTIME(unix_timestamp, format)

Convertit le timestamp passé en paramètre en date, dans le format spécifié par format.

I-F - Ajout/soustraction d'intervalles de temps

Pour l'ajout/soustraction sur des dates/heures, on dispose de plusieurs fonctions :

Nom

Description

Restrictions
de version

DATE_ADD()

DATE_ADD(date, INTERVAL nb uniteDeTemps)

Ajoute nb unités de temps à la date passée en paramètre.

Les différentes valeurs d'uniteDeTemps sont données dans le tableau suivant.

Synonyme: ADDDATE(date, INTERVAL nb uniteDeTemps).

DATE_SUB()

DATE_SUB() se comporte comme DATE_ADD, mais pour la soustraction.

Synonyme: SUBDATE(date, INTERVAL nb uniteDeTemps).

ADDTIME()

ADDTIME(date, expr) ajoute l'expression expr à la date/heure passée en paramètre.

Exemple:
SELECT ADDTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002');
-> '1998-01-02 01:01:01.000001'

4.1.1 +
(rétro)

SUBTIME()

SUBTIME() se comporte comme ADDTIME, mais pour la soustraction.

4.1.1 +
(rétro)

Et voici les différentes valeurs que peut prendre uniteDeTemps dans DATE_ADD() et DATE_SUB() :

Unité de temps

Exemple

Restrictions
de version

MICROSECOND

125 MICROSECOND

4.1.1 +

SECOND

20 SECOND

MINUTE

30 MINUTE

HOUR

2 HOUR

DAY

3 DAY

WEEK

3 WEEK

5.0.0 +

MONTH

2 MONTH

QUARTER

3 QUARTER

5.0.0 +

YEAR

2 YEAR

SECOND_MICROSECOND

20.125 SECOND_MICROSECOND

4.1.1 +

MINUTE_MICROSECOND

30:20.125 MINUTE_MICROSECOND

4.1.1 +

MINUTE_SECOND

30:20 MINUTE_SECOND

4.1.1 +

HOUR_MICROSECOND

2:30:20.125 HOUR_MICROSECOND

4.1.1 +

HOUR_SECOND

2:30:20 HOUR_SECOND

4.1.1 +

HOUR_MINUTE

2:30 HOUR_MINUTE

DAY_MICROSECOND

3 2:30:20.125 DAY_MICROSECOND

4.1.1 +

DAY_SECOND

3 2:30:20 DAY_SECOND

DAY_MINUTE

3 2:30 DAY_MINUTE

DAY_HOUR

3 2 DAY_HOUR

YEAR_MONTH

1-6 YEAR_MONTH

I-G - Différences entre deux dates/heures

Pour connaitre l'intervalle de temps entre deux dates/heures, on dispose de fonctions telles que DATEDIFF() ou TIMEDIFF().

Nom

Description

Restrictions
de version

DATEDIFF()

DATEDIFF(expr,expr2)

Retourne le nombre de jours entre la date de début expr et la date de fin expr2. expr et expr2 sont des expressions de type DATE ou DATETIME. Seule la partie DATE est utilisée dans le calcul. Le résultat peut être négatif.

SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1

4.1.1 +
(rétro)

TIMEDIFF()

TIMEDIFF(expr,expr2)

Retourne la durée entre l'heure de début expr et l'heure de fin expr2. expr et expr2 sont des expressions de type TIME ou DATETIME, et doivent être du même type

SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'

4.1.1 +
(rétro)

I-H - Comparaison de dates/heures

Il existe plusieurs manières d'effectuer une comparaison entre deux dates/heures :

SELECT *
FROM produit

WHERE dateLimite < CURDATE() ;

SELECT *
FROM produit

WHERE dateLimite BETWEEN CURDATE() AND  DATE_ADD(CURDATE(), INTERVAL 3 DAY) ;

SELECT *
FROM personne

WHERE DATE_FORMAT(dateNaissance, "%d-%m") = DATE_FORMAT(CURDATE(), "%d-%m")

- avec les opérateurs de comparaison ==, !=, <, <=, >, >= ;

- pour les intervalles de temps, à l'aide de l'opérateur BETWEEN ;

- à l'aide de n'importe quelle fonction sur les dates/heures.

Cette section constitue un récapitulatif des fonctions de date/heures les plus couramment utilisées en MySQL.

Dans la section suivante, nous étudierons de nombreux problèmes récurrents
ayant recours à des manipulations de dates.

I-I - Études de cas

Cette partie présente quelques cas de manipulation de dates, inspirés de problèmes réels :

- Réservation de chambre d'hôtel ;

- Requêtes sur un historique ;

- Statistiques sur une période donnée ;

- Anniversaires ;

- Horaires flottants, Tranches Horaires ;

- Temps total de réalisation d'une tâche ;

- Mise à jour automatique des dates de modification d'enregistrements.

Cas considérés

Réservation d'une chambre d'hôtel

Énoncé du problème

Un hôtel contient des chambres. Ces chambres sont réservées pour une période plus ou moins longue. On ne peut réserver une chambre si quelqu'un a déjà réservé la chambre sur la même période. En fonction de la période désirée, on veut afficher les chambres disponibles.

Ce problème s'applique à tous types de réservations (salles, matériel, formateurs…).

Pour représenter ce problème, on dispose des tables suivantes :

- Chambre : les chambres ;

- Réservation : les réservations, avec numéro de chambre, id du client, date de début, de fin ;

- Client : les clients de l'hôtel.

Script de création

CREATE TABLE chambre (
numero INT PRIMARY KEY AUTO_INCREMENT,
) ;

CREATE TABLE client (
id  INT PRIMARY KEY AUTO_INCREMENT,
nom CHAR(30) NOT NULL
) ;

CREATE TABLE reservation (
numeroChambre INT  NOT NULL,
idClient      INT  NOT NULL,
dateDebut     DATE NOT NULL,
dateFin       DATE NOT NULL,

PRIMARY KEY(numeroChambre, idClient, dateDebut),
CONSTRAINT FOREIGN KEY fk_numeroChambre (numeroChambre) REFERENCES chambre(numero),
CONSTRAINT FOREIGN KEY fk_idClient      (idClient)      REFERENCES client(id)
)

Insertion des données

INSERT INTO chambre
VALUES
(1),
(2),
(3) ;

INSERT INTO client
VALUES
(1, "pcaboche"),
(2, "Maximillian"),
(3, "Debug") ;

INSERT INTO reservation
(1, 1, "2006-07-03", "2006-07-16"),
(1, 2, "2006-07-17", "2006-07-30"),
(2, 3, "2006-07-10", "2006-07-23") ;

La requête

Pour ce problème, on dispose de deux variables : $debut et $fin (il s'agit de variables issues de l'application préparant la requête, ici une application PHP). Ces variables représentent respectivement les dates de début et de fin de la période pour laquelle on souhaite effectuer une réservation.

Bien entendu, il faut que l'on ait la relation $debut < $fin pour que la requête marche. Cette vérification doit être effectuée dans le programme appelant la requête.

Pour que la réservation soit possible, il faut que les intervalles [$debut, $fin] et [reservation.dateDebut, reservation.dateFin] ne se chevauchent pas.

Pour cela, on part de l'ensemble des chambres, duquel on enlève les chambres pour lesquelles on a au moins une réservation durant la période considérée. Comme en MySQL on ne dispose pas de l'opérateur EXCEPT (ou MINUS), on fait cela avec une jointure LEFT OUTER JOIN et un test IS NULL.

Voici donc finalement la requête :

SELECT chambre.*

FROM chambre

LEFT OUTER JOIN reservation
ON (
chambre.numero = reservation.numeroChambre

AND (
$debut BETWEEN reservation.dateDebut AND reservation.dateFin
OR $fin   BETWEEN reservation.dateDebut AND reservation.dateFin
OR reservation.dateDebut BETWEEN $debut AND $fin
OR reservation.dateFin   BETWEEN $debut AND $fin
)
)

WHERE reservation.numeroChambre IS NULL

Cette requête retournera la liste des chambres libres sur la période considérée.

Évènements les plus récents d'un historique

Énoncé du problème

On dispose d'une table historique contenant l'historique des évènements survenus sur un ensemble d'éléments. Nous prendrons comme exemple du matériel informatique, cependant les requêtes SQL peuvent s'adapter à n'importe quel exemple.

Les éléments considérés seront donc des PC, et les évènements associés seront par exemple: des réparations, des mises à jour, des réinstallations, etc.

On cherche à avoir la liste des derniers évènements survenus pour chacun des matériels.

Pour ce faire, on dispose des tables suivantes :

- Materiel : le matériel informatique (éléments) ;

- Type_Evenement : les évènements ;

- Historique : l'historique des évènements survenus sur le matériel (association entre Matériel et Type_Evenement).

Script de création

CREATE TABLE materiel (
id          INT PRIMARY KEY AUTO_INCREMENT,
description CHAR(30) NOT NULL
) ;

CREATE TABLE type_evenement (
id      INT PRIMARY KEY AUTO_INCREMENT,
libelle CHAR(30) NOT NULL
) ;

CREATE TABLE historique (
id            INT PRIMARY KEY AUTO_INCREMENT,
idMat         INT NOT NULL,
idEvent       INT NOT NULL,
datetimeEvent DATETIME,
gravite       INT NOT NULL DEFAULT "0",
commentaires  VARCHAR(100) NOT NULL DEFAULT "",

CONSTRAINT FOREIGN KEY fk_idMat   (idMat)   REFERENCES materiel(id),
CONSTRAINT FOREIGN KEY fk_idEvent (idEvent) REFERENCES type_evenement(id)
) ;

Insertion des données

INSERT INTO materiel (id, description)
VALUES
(1, "PC 1"),
(2, "PC 2"),
(3, "PC 3"),
(4, "PC 4"),
(5, "PC 5"),
(6, "PC 6");

INSERT INTO type_evenement (id, libelle)
VALUES
(1, "Première mise en service"),
(2, "Dépannage sur site"),
(3, "Réparation atelier"),
(4, "Retour fournisseur"),
(5, "Mise à jour OS"),
(6, "Réinstallation OS"),
(7, "Scan antivirus"),
(8, "Traitement antivirus") ;

INSERT INTO historique (idMat, idEvent, datetimeEvent, gravite, commentaires)
VALUES
(1, 1, "2005-06-01 14:00:00", 0, ""),
(2, 1, "2005-06-01 15:30:00", 0, ""),
(3, 1, "2005-07-01 10:45:00", 0, ""),
(4, 1, "2005-07-15 09:00:00", 0, ""),
(4, 3, "2005-09-21 10:15:00", 3, "Un membre du service commercial a pris son lecteur CD pour un porte-goblet!"),
(2, 2, "2005-09-24 08:10:00", 2, "L'ordi de la secrétaire ne démarrait pas: le cable était débranché..."),
(3, 7, "2005-09-26 00:08:00", 1, ""),
(1, 5, "2005-10-01 09:00:00", 1, "Mise à jour Service Pack 2"),
(1, 6, "2005-10-01 10:30:00", 4, "La mise à jour a planté..."),
(1, 7, "2006-01-09 00:02:00", 1, ""),
(2, 7, "2006-01-09 00:08:00", 1, ""),
(3, 7, "2006-01-09 00:56:00", 1, ""),
(4, 7, "2006-01-09 01:04:00", 1, ""),
(3, 8, "2006-01-09 09:15:00", 5, "Un imbécile a téléchargé un fichier illégal contenant un virus."),
(3, 6, "2006-01-09 16:35:00", 5, "L'imbécile s'est fait viré...") ;

Par ailleurs, le problème faisant intervenir un nombre important de paramètres, nous ajouterons des contraintes pas à pas (sous forme de raffinements), avant d'aboutir à notre requête finale.

Requête de base

Pour chacun des éléments dans l'historique, on cherche à déterminer le dernier évènement survenu. Pour cela, on effectue la requête suivante :

SELECT H1.*

FROM historique H1
LEFT OUTER JOIN historique H2
ON (H1.idMat = H2.idMat AND H1.datetimeEvent < H2.datetimeEvent)

WHERE H2.datetimeEvent IS NULL
ORDER BY H1.idMat ;

Explications : On part de l'historique H1 et pour chaque évènement on cherche les autres évènements (H2) portant sur le même matériel, mais plus récents (jointure LEFT JOIN réflexive). Le dernier évènement n'aura pas de correspondance dans H2, d'où la condition WHERE H2.datetimeEvent IS NULL

De nombreuses personnes tendent à exprimer ce traitement au moyen de requêtes imbriquées, cependant :

- les requêtes imbriquées ne sont implémentées qu'à partir de la version 4.1 de MySQL ;

- les requêtes imbriquées tendent à rendre les exécutions moins rapides ;

- les requêtes imbriquées ne permettent pas d'exprimer les raffinements suivants.

Raffinement 1 : les N derniers évènements de l'historique

Cette fois-ci, on cherche une requête plus générale pour afficher les N derniers évènements survenus sur chaque matériel. Dans notre exemple, N=3 :

SELECT H1.*

FROM historique H1
LEFT OUTER JOIN historique H2
ON (H1.idMat=H2.idMat AND H1.datetimeEvent <= H2.datetimeEvent)

GROUP BY H1.id
HAVING COUNT(*) <= 3
ORDER BY H1.idMat, H1.datetimeEvent ;

Explications : ici, on fait presque la même jointure que précédemment (sauf que le "<" de la jointure est remplacé par un "<="), puis on regroupe par évènement et on ne garde que ceux ayant au plus N évènements plus récents (condition du HAVING).

Raffinement 2 : prise en compte du matériel n'apparaissant pas dans l'historique

Pour l'instant, pour des raisons de simplicité, nous ne nous sommes intéressés qu'aux matériels pour lesquels on avait déjà un enregistrement dans l'historique. Cependant, il existe des matériels qui n'ont pas de correspondance dans l'historique (PC 5 et 6). On souhaite maintenant tenir compte des matériels qui n'apparaissent pas dans l'historique.

Pour cela, on utilise une jointure de type LEFT OUTER JOIN (s'il n'y a pas de correspondance dans l'historique, les colonnes prendront la valeur NULL)

SELECT materiel.* , H1.*
FROM materiel

LEFT OUTER JOIN historique H1
ON ( materiel.id = H1.idMat )

LEFT OUTER JOIN historique H2
ON ( H1.idMat = H2.idMat AND H1.datetimeEvent <= H2.datetimeEvent )

GROUP BY materiel.id, H1.id
HAVING COUNT(*) <= 3
ORDER BY materiel.id, H1.datetimeEvent

Explications : on part de l'ensemble des matériels répertoriés, puis on recherche s'il existe des évènements correspondant dans l'historique (jointure LEFT JOIN). Si aucun évènement n'est trouvé dans l'historique, les colonnes de H1 prendront la valeur NULL. Pour le reste, il s'agit des mêmes jointures que dans le raffinement précédent.

Raffinement 3 : spécifications multicritères

Maintenant, on souhaite afficher les N derniers évènements de gravité supérieure à 3 pour tous les matériels (y compris ceux qui n'apparaissent pas dans l'historique).

Dit autrement, c'est comme si on souhaitait effectuer les mêmes requêtes que précédemment, mais sur un historique réduit aux seuls évènements de gravité supérieure à 3.

Pour cela, on effectue la requête suivante :

SELECT materiel.*, H1.*
FROM materiel

LEFT OUTER JOIN historique H1
ON ( materiel.id = H1.idMat
AND 3 < H1.gravite)

LEFT OUTER JOIN historique H2
ON ( H1.idMat = H2.idMat AND H1.datetimeEvent <= H2.datetimeEvent
AND 3 < H2.gravite)

GROUP BY materiel.id, H1.id
HAVING COUNT(*) <= 3
ORDER BY materiel.id, H1.datetimeEvent

Remarquez la place des conditions 3<gravite : elles se trouvent dans les conditions de jointures.

Explications : quand on fait nos jointures, c'est entre un ensemble de départ et un ensemble réduit à certaines données (par exemple, entre l'ensemble des matériels et l'ensemble des évènements réduit à ceux de gravité supérieure à 3). Par conséquent, les conditions de réduction de domaine sont situées dans les conditions de jointures LEFT JOIN.

Si on mettait ces conditions dans une clause WHERE, la signification serait complètement différente (l'ensemble des évènements de gravité supérieure à 3 faisant partie des N derniers évènements). Par ailleurs, on aurait un problème de suppression de lignes pour les matériels n'ayant pas d'évènements correspondants…

Statistiques sur une période donnée

Énoncé du problème

On a une liste de messages échangés. On désire effectuer des statistiques sur le nombre de messages envoyés par jour sur les 30 derniers jours.

Si pour certains jours aucun message n'a été envoyé, le jour doit quand même apparaître dans les statistiques, avec "0" comme nombre de messages

On dépose des tables suivantes :

- Message : les messages échangés ;

- Utilisateur : les utilisateurs.

Script de création

CREATE TABLE message (
num             INT PRIMARY KEY AUTO_INCREMENT,
idExpediteur    INT  NOT NULL,
idDestinataire  INT  NOT NULL,
sujet           VARCHAR(50) NOT NULL,
contenu         TEXT NOT NULL,
datetimeEnvoi   DATETIME NOT NULL,

CONSTRAINT FOREIGN KEY fk_idExpediteur   (idExpediteur)   REFERENCES utilisateur(id),
CONSTRAINT FOREIGN KEY fk_idDestinataire (idDestinataire) REFERENCES utilisateur(id)
) ;

Prérequis

Pour nous faciliter la tâche, nous allons créer une table contenant un certain nombre de valeurs numériques comprises entre 0 et N.

Script de création + insertion

CREATE TABLE nbr (
n INT PRIMARY KEY
) ;

INSERT INTO nbr VALUES
(0), (1), (2), (3), (4), (5) ;

INSERT IGNORE INTO nbr
SELECT A.n*6 + B.n AS Valeur
FROM nbr A, nbr B ;

Maintenant nous disposons d'une liste des entiers de 0 à 35, ce qui est largement suffisant pour ce que nous souhaitons faire.

La requête

Maintenant, nous disposons de tout ce qu'il nous faut pour effectuer la requête: la table nbr va nous permettre de fabriquer un intervalle de temps allant de (aujourd'hui-30 jours) à la date actuelle. Par ailleurs, le champ de date dans message est de type DATETIME, il nous faudra que la partie "DATE" pour faire la jointure. Ce sera fait grâce à la fonction DATE.

Voici donc notre requête :

SELECT
DATE_SUB(CURDATE(), INTERVAL nbr.n DAY) AS jour,
COUNT(message.num) AS nombreMessages

FROM nbr
LEFT OUTER JOIN message
ON DATE(message.datetimeEnvoi) = DATE_SUB(CURDATE(), INTERVAL nbr.n DAY)

WHERE nbr.n BETWEEN 0 AND 30
GROUP BY jour
ORDER BY jour

Note de version : la fonction DATE est apparue avec la version 4.1.1 de MySQL. Pour les versions antérieures, il faudra utiliser la fonction DATE_FORMAT(message.datetimeEnvoi, "%Y-%m-%d"). Voir partie Rétrocompatibilité pour plus d'informations.

Dans la pratique

Dans la pratique, sur un nombre important d'enregistrements, il se peut que la requête soit lente à cause du GROUP BY. C'est d'autant plus vrai si les dates ne sont pas indexées (les index servent à accélérer les comparaisons sur les champs, par l'usage d'un arbre binaire de recherche).

Pour remédier à ce problème, il peut être judicieux d'avoir recours à des requêtes de type SELECT COUNT(*) pour chacun des intervalles considérés plutôt qu'à une seule requête pour l'ensemble de la période. En effet, l'opération COUNT est une opération simple et nécessitant peu de ressources. Par ailleurs, cela laisse au programmeur une grande liberté et une grande souplesse concernant la période pour laquelle on souhaite établir les statistiques (début, fin et pas d'incrémentation).

En résumé, il a été vérifié dans la pratique que pour un nombre important d'enregistrements, il est préférable d'effectuer plusieurs requêtes SELECT COUNT(*) dans une boucle for. Cette boucle se programme au niveau de l'applicatif (ou éventuellement sous forme de procédure stockée).

Anniversaires

Énoncé du problème

On désire avoir la liste de tous les utilisateurs dont c'est l'anniversaire aujourd'hui.

On désire aussi connaitre leur âge.

On dispose de la table suivante :

- Utilisateur : liste des utilisateurs.

Script de création

CREATE TABLE utilisateur (
id            INT PRIMARY KEY AUTO_INCREMENT,
nom           INT  NOT NULL,
dateNaissance DATE NOT NULL
) ;

La requête

Voici la requête qui permet cela :

SELECT
nom,
YEAR(CURDATE()) - YEAR(dateNaissance) AS Age

FROM utilisateur
WHERE DATE_FORMAT(dateNaissance, "%d-%m") = DATE_FORMAT(CURDATE(), "%d-%m")

Bien sûr, si on est une année non bissextile, le 1er mars il faudra souhaiter les anniversaires de ceux nés un 29 février. Pour cela, il faudra compléter la requête.

Voici le code PHP qui permet cela :

$now = localtime(time(), true) ;

if ((date("d-m") == "01-03") && !is_leap_year($now['tm_year']) ) {
$sql_query .= 'OR DATE_FORMAT(BirthDay, "%d-%m") = "29-02"' ;
}

Et voici le code de la fonction is_leap_year :

Fonction is_leap_year($year)

function is_leap_year($year) {
return checkdate(02, 29, $year) ;
}

Horaires Glissants, Tranches Horaires

Énoncé du problème

On désire créer un site de jeux en ligne. Voici les règles :

- le site propose plusieurs jeux ;
- un joueur peut jouer à un jeu s’il n'a jamais joué à ce jeu ou s’il s'est écoulé un temps suffisamment long depuis sa dernière participation.

On a deux méthodes de calcul pour déterminer si le temps qui s'est écoulé entre deux participations est suffisamment long : "intervalles glissants" et "par tranches horaires".

Avec la méthode intervalles glissants si le joueur joue à 14h25 et que l'intervalle est fixé à 1 heure, il pourra rejouer à 15h35.

Avec la méthode par tranches horaires si le joueur joue à 14h25 et que l'intervalle est fixé par tranches d'une heure, il pourra rejouer à 15h00.

Pour représenter ce problème, on dispose des tables suivantes :

- Joueur : les joueurs ;

- Jeu : les jeux, avec stipulé :

- la méthode de calcul utilisée, de type: ENUM("Glissant", "Tranche_Horaire"),

- le délai entre deux parties, exprimé en secondes ;

- Participe_Jeu : heure de dernière participation d'un joueur à un jeu.

Script de création

CREATE TABLE joueur (
idJoueur     INT UNSIGNED      NOT NULL AUTO_INCREMENT,
nom          CHAR(30)          NOT NULL,

PRIMARY KEY (idJoueur)
) ;

CREATE TABLE jeu (
idJeu        INT UNSIGNED      NOT NULL AUTO_INCREMENT,
nom          CHAR(30)          NOT NULL,
description  CHAR(255)         NOT NULL,
delaiMinSec  INT UNSIGNED      NOT NULL,
methCalcul   ENUM("Glissant", "Tranche_Horaire") NOT NULL,

PRIMARY KEY (idJeu)
) ;

CREATE TABLE participe_jeu (
idJoueur       INT UNSIGNED NOT NULL,
idJeu          INT UNSIGNED NOT NULL,
heureParticipe DATETIME,

PRIMARY KEY (idJoueur, idJeu),

CONSTRAINT FOREIGN KEY fk_idJoueur (idJoueur) REFERENCES joueur(idJoueur),
CONSTRAINT FOREIGN KEY fk_idJeu    (idJeu)    REFERENCES client(idJeu)
) ;

La requête

Pour des raisons de simplicité, nous avons choisi la méthode suivante :

- la requête SQL nous renseignera sur l'heure actuelle (heure du serveur MySQL) et l'heure de dernière participation à un jeu. Ces heures seront retournées sous forme de UNIX_TIMESTAMP ;

- c'est l'application qui calculera le temps d'attente avant la prochaine participation. Si ce temps est négatif ou nul, alors il est possible de participer immédiatement.

Voici la requête qui va nous donner la liste des dernières participations du joueur numéro 12 :

SELECT
jeu.*,
UNIX_TIMESTAMP( NOW() )                      AS ts_actuel,
UNIX_TIMESTAMP(participe_jeu.heureParticipe) AS ts_participe

FROM jeu

LEFT OUTER JOIN participe_jeu
ON  (participe_jeu.idJeu=jeu.idJeu AND participe_jeu.idJoueur=12)

Le résultat renvoyé est du genre :

idJeu

nom

description

delaiMinSec

methCalcul

ts_actuel

ts_participe

1

Jeu 1

Jeu 1

3600

Glissant

1138455932

1138448285

2

Jeu 2

Jeu 2

3600

Tranche_Horaire

1138455932

NULL

3

Jeu 3

Jeu 3

86400

Tranche_Horaire

1138455932

1138448285

Et voici la fonction (PHP) qui va nous permettre de calculer le temps d'attente avant la nouvelle partie :

Calcul du temps d'attente

function tempsAttente($methCalcul, $ts_actuel, $ts_participe, $delaiMinSec) {

$tempsAttente = 0 ;

if (!is_null($ts_participe)) {

if ($methCalcul == "Tranche_Horaire") {

$tempsAttente =
$ts_participe - $ts_participe % $delaiMinSec
+ $delaiMinSec
- $ts_actuel ;

} else {
# Glissant par défaut

$tempsAttente =
$ts_participe
+ $delaiMinSec
- $ts_actuel ;
}

}

return $tempsAttente ;
}

Pour les intervalles glissants, il suffit d'ajouter et de soustraire des intervalles de temps. Pour le calcul par tranches horaires on utilise l'opérateur modulo (%) avec le délai minimum, exprimé en secondes.

Temps total de réalisation d'une tâche

Énoncé du problème

On connait le temps de réalisation des différentes étapes d'une tâche (exprimés sous forme d'un champ de type TIME). On souhaite connaitre le temps total d'exécution de la tâche.

La requête

Pour pouvoir ajouter les champs TIME entre eux, on va d'abord les convertir en nombre de secondes avant d'en faire la somme. Ensuite, on fera la conversion inverse afin d'obtenir le temps total dans un format lisible.

Voici la requête correspondante :

SELECT
SEC_TO_TIME( SUM(TIME_TO_SEC(tempsRealisation)) ) AS TempsTotal

FROM etape
GROUP BY tache

Mise à jour automatique des dates de modification d'enregistrement

Énoncé du problème

On souhaite créer une table avec un champ pour connaitre la date de dernière modification de l'enregistrement (avec mise à jour automatique).

MySQL version 4.1.2+

La mise à jour automatique de la date de modification est possible à partir de la version 4.1.2.

On précise ceci dans le script de création de la table :

Script de création

CREATE TABLE t (
tsModif   TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT   CURRENT_TIMESTAMP
) ;

Dans ce script :

- DEFAULT CURRENT_TIMESTAMP précise que la date sera automatiquement mise à jour lors de la création de l'enregistrement (requête INSERT) ;

- ON UPDATE CURRENT_TIMESTAMP précise que la date sera automatiquement mise à jour à chaque mise à jour de l'enregistrement (requête UPDATE).

Ceci n'est vrai que pour les champs de type TIMESTAMP. Pour tous les autres types de champs (y compris DATE, TIME et DATETIME), la valeur par defaut doit être une constante.

Pour plus de renseignements, voir ce lien : http://dev.mysql.com/doc/refman/6.0/en/data-type-defaults.html

Rétrocompatibilité

Pour les versions antérieures à la 4.1.2, il faudra gérer dans l'application la mise à jour du champ de date, grâce à la fonction NOW() (ou une autre fonction adaptée au besoin).

Insertion

INSERT INTO t (tsCreation, tsModif)
VALUES (NOW(), NOW()) ;

Mise à jour

UPDATE TABLE t
SET tsModif=NOW() ;

II - Rétrocompatibilité

Pallier l'absence de ADDTIME()/SUBTIME()

Pour pallier l'absence des fonctions ADDTIME()/SUBTIME(), (disponibles à partir de la version 4.1.1) il est possible d'avoir recours aux fonctions UNIX_TIMESTAMP et FROM_UNIXTIME.

Ainsi, on prend la date désirée, la convertit en timestamp, ajoute le nombre de secondes désirées et reconvertit le résultat en date/heure grâce à la fonction FROM_UNIXTIME.

SELECT
FROM_UNIXTIME(UNIX_TIMESTAMP(uneDate) +3600) AS UneHeurePlusTard

FROM uneTable ;

Pallier l'absence de DATEDIFF()/TIMEDIFF()

Pour pallier l'absence des fonctions DATEDIFF()/TIMEDIFF(), (disponibles à partir de la version 4.1.1) on a également recours à la fonction UNIX_TIMESTAMP.

Cette fonction retourne le nombre de secondes depuis le 1er janvier 1970. En faisant une soustraction, on obtient le nombre de secondes entre deux dates. Cependant UNIX_TIMESTAMP retourne un entier non signé et donc pour que la soustraction ne renvoie pas d'erreur en cas de résultat négatif, on est obligé de faire un CAST(… AS SIGNED).

Pour obtenir un nombre de jours, on divise le résultat obtenu par le nombre de secondes dans une journée (soit 86400) :

SELECT
FLOOR (
(  CAST(UNIX_TIMESTAMP(date1) AS SIGNED)
- CAST(UNIX_TIMESTAMP(date2) AS SIGNED)
) / 86400
) AS NbJourDiff

FROM uneTable ;

Pallier l'absence de DATE()

La fonction DATE() extrait la partie "date" d'un champ DATETIME (ou d'un champ DATE !). Elle a été introduite avec MySQL 4.1.1.

Il est possible de pallier son absence en faisant un simple DATE_FORMAT(expr, "%Y-%m-%d").

III - Bêtisier : les erreurs à ne pas faire

Cette section illustre les erreurs à ne PAS faire.

J'ai préféré faire une section à part, de manière à séparer clairement les exemples "corrects" de ceux à ne pas reproduire.

J'ai ajouté cette section non pas pour me moquer, mais pour :

- illustrer certaines erreurs courantes avec les fonctions de date ;

- comprendre comment MySQL évalue les résultats renvoyés par les fonctions de date/heure ;

- vous mettre en garde sur les choses à ne pas faire.

Par ailleurs, comme le dit le proverbe :

"L'homme sage apprend de ses erreurs. L'homme encore plus sage apprend des erreurs des autres."

Je tiens à remercier toutes les personnes qui, par leurs erreurs, ont contribué à enrichir cette section, et dont je tairai les noms…

Erreur d'ajout de temps

Énoncé du problème

Un utilisateur cherche à mettre à jour un champ date en ajoutant un certain nombre de minutes à la date courante. Voici la requête qu'il utilise :

NE PAS REPRODUIRE !

UPDATE unetable
SET unedate=NOW()+120

L'utilisateur pense ainsi ajouter 2 minutes (120 s) à la date courante, mais cela lui retourne la date 0000-00-00 00:00:00 à la place.

Origine de l'erreur

Au lieu d'ajouter 2 minutes, on va chercher à ajouter 2 heures (7200 s). À titre de comparaison, on va aussi mettre l'heure actuelle. La requête effectuée est la suivante :

SELECT NOW(), NOW()+7200

Et voici le résultat renvoyé :

NOW()        |   NOW()+7200
-------------------------------------
2006-01-01 16:01:28 | 20060101167328

Comme on peut le constater, NOW()+7200 renvoie la date "2006-01-01 16:73:28", qui n'est pas une date correcte !

En fait, voilà ce qu'il se passe: NOW() renvoie une date, convertie en entier dans le format YYYYMMDDHHMMSS. Quand on fait l'addition, cela peut renvoyer un chiffre qui correspond à une date invalide et sera évaluée en 0000-00-00 00:00:00 (avec 7200, la date est toujours invalide. Avec 120, ça dépend de l'heure à laquelle on lance la requête !)

Pour faire un ajout d'intervalle de temps, il faut donc SYSTÉMATIQUEMENT utiliser les fonctions prévues à cet effet (ou à défaut, les solutions prévues dans la section Rétrocompatibilité).