Fonctions de date/heure en MySQL

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.

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.

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).

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.

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…

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.

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.

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, 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).

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 :

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

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 :

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

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.

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

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 :

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

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).

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

UPDATE TABLE t 
SET tsModif=NOW() ;

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 :

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.

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é).