8.4.3.5 Calculs sur les dates

MySQL possède de puissantes fonctions pour effectuer des calculs sur les dates, comme par exemple, calculer un age, ou extraire des parties de date.

Pour déterminer l'age de chacun des animaux, il faut calculer la différence entre la naissance et la date courante. Puis, convertir ces deux dates en jours, et diviser le tout par 365, pour avoir le nombre d'année.

mysql> SELECT nom, (TO_DAYS(NOW())-TO_DAYS(naissance))/365 FROM animaux;
+----------+-----------------------------------------+
| nom      | (TO_DAYS(NOW())-TO_DAYS(naissance))/365 |
+----------+-----------------------------------------+
| Fluffy   |                                    6.15 |
| Claws    |                                    5.04 |
| Buffy    |                                    9.88 |
| Fang     |                                    8.59 |
| Bowser   |                                    9.58 |
| Chirpy   |                                    0.55 |
| Whistler |                                    1.30 |
| Slim     |                                    2.92 |
| Puffball |                                    0.00 |
+----------+-----------------------------------------+

Bien que cette requête fasse bien ce qu'on lui demande, il y a de la place pour quelques améliorations. En premier lieu, les résultats gagneraient à être classés. De plus, le titre de la colonne n'est pas très explicite.

Le premier problème peut être résolu avec une clause ORDER BY nom qui va classer par ordre alphabétique. Pour régler le problème du titre, nous allons utiliser un alias.

mysql> SELECT nom, (TO_DAYS(NOW())-TO_DAYS(naissance))/365 AS age
    -> FROM animaux ORDER BY nom;
+----------+------+
| nom      | age  |
+----------+------+
| Bowser   | 9.58 |
| Buffy    | 9.88 |
| Chirpy   | 0.55 |
| Claws    | 5.04 |
| Fang     | 8.59 |
| Fluffy   | 6.15 |
| Puffball | 0.00 |
| Slim     | 2.92 |
| Whistler | 1.30 |
+----------+------+

Pour trier les résultats par age plutôt que par nom nom, il suffit de le mettre dans la clause ORDER BY :

mysql>  SELECT nom, (TO_DAYS(NOW())-TO_DAYS(naissance))/365 AS age
    ->  FROM animaux ORDER BY age;
+----------+------+
| nom     | age  |
+----------+------+
| Puffball | 0.00 |
| Chirpy   | 0.55 |
| Whistler | 1.30 |
| Slim     | 2.92 |
| Claws    | 5.04 |
| Fluffy   | 6.15 |
| Fang     | 8.59 |
| Bowser   | 9.58 |
| Buffy    | 9.88 |
+----------+------+

Une requête similaire pourrait calculer l'age de mort des animaux morts. Pour cela, vous allez déterminer les animaux morts, en testant la colonne mort à NULL. Puis, pour les valeurs non-NULL, calculez l'age avec les colonnes mort et naissance:

mysql>  SELECT nom, naissance, mort, (TO_DAYS(mort)-TO_DAYS(naissance))/365 AS age
    ->  FROM animaux WHERE mort IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| nom    | naissance  | mort       | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5.91 |
+--------+------------+------------+------+

La requête utilise mort IS NOT NULL plutôt que mort != NULL car NULL est une valeur spéciale. Cela est expliqué plus loin. Allez 8.4.3.6 Travailler avec la valeur NULL.

Et comment rechercher les animaux dont l'anniversaire sera le mois prochain ? Pour ce genre de calculs, year et day sont inutiles, il suffit d'extraire le mois de la colonne naissance . MySQL fournit plusieurs fonctions d'extraction comme par exemple YEAR(), MONTH() et DAY(). MONTH() est le plus approprié ici. Pour voir comment cela fonction, exécutez la commande suivante, qui naissance et MONTH(naissance):

mysql> SELECT nom, naissance, MONTH(naissance) FROM animaux;
+----------+------------+------------------+
| nom      | naissance  | MONTH(naissance) |
+----------+------------+------------------+
| Fluffy   | 1993-02-04 |                2 |
| Claws    | 1994-03-17 |                3 |
| Buffy    | 1989-05-13 |                5 |
| Fang     | 1990-08-27 |                8 |
| Bowser   | 1989-08-31 |                8 |
| Chirpy   | 1998-09-11 |                9 |
| Whistler | 1997-12-09 |               12 |
| Slim     | 1996-04-29 |                4 |
| Puffball | 1999-03-30 |                3 |
+----------+------------+------------------+

Trouver les animaux dont la date de naissance est le mois prochain est facile. En supposant que nous soyons au mois d'avril. Alors, le mois est le 4, et il suffit de rechercher les animaux nés au mois de May (5), comme ceci :

mysql> SELECT nom, naissance FROM animaux WHERE MONTH(naissance) = 5;
+-------+----------------+
| nom   | naissance      |
+-------+----------------+
| Buffy | 1989-05-13     |
+-------+----------------+

Il y a bien sur un cas particulier: décembre. Il ne suffit pas seulement d'ajouter 1 à numéro du mois courant et de chercher les dates d'anniversaires correspondantes, car personne ne nait au mois 13. A la place, il faut chercher les animaux qui sont nés au mois de janvier.

Vous pourriez écrire une requête qui fonctionne, quelque soit le mois courant. De cette façon, vous n'aurez pas à utiliser un numéro particulier de mois dans la requête. DATE_ADD() vous permettra d'ajouter une durée de temps à une date. Si vous ajoutez un mois à la date de NOW(), puis vous en sortez le mois avec MONTH(), le résultat sera bien le mois suivant.

mysql> SELECT nom, naissance FROM animaux
    -> WHERE MONTH(naissance) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Une autre manière de faire serait d'ajouter 1 au mois courant, puis d'utiliser la (MOD) pour ``boucler'' à la fin de l'année, et faire correspondre janvier et décembre :

mysql> SELECT nom, naissance FROM animaux
    -> WHERE MONTH(naissance) = MOD(MONTH(NOW()),12) + 1;