Le type INTERVAL de SQL et ses dangers

8 réponses

De nombreux développeurs trouvent intéressant le type INTERVAL de la norme SQL pour stocker des durées temporelles. Ce type fait partie de la norme SQL. pourtant certains éditeurs de SGBDR se sont interdit de le mettre en Å“uvre… Pourquoi ?

Le type INTERVAL permet de définir des durées. Soit en précisant une granularité de temps (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND) et sa limite, soit en précisant une granularité de départ (et sa limite) et une granularité de fin avec sa précision. Cependant le type INTERVAL ne prend pas en compte toutes les combinaisons possible entre granularité, mais seulement celles-ci :

YEAR(p)
YEAR(p) TO MONTH
MONTH(p)
DAY(p)
DAY(p) TO HOUR
DAY(p) TO MINUTE
DAY(p) TO SECOND(s)
HOUR(p)
HOUR(p) TO MINUTE
HOUR(p) TO SECOND(s)
MINUTE(p)
MINUTE(p) TO SECOND(s)
SECOND(p, s)

Ou p est la précision de la granularité maximal de durée et s la précision de la granularité minimale de durée.
Par exemple DAY(3) TO SECOND (4) est une durée maximale de 999 jours avec une précision au 10000e de seconde.

Premier problème…

Vous noterez immédiatement un énorme trou : il n’est pas possible de définir une durée avec une granularité de MOIS intermédiaire, par exemple un YEAR(p) TO MINUTE… La raison est simple : de toutes les granularités de temps, le mois possède une particularité… Celui de ne pas compter toujours le même nombre de jours ! C’est pourquoi la norme SQL interdit d’utiliser une durée « traversant le mois »… Autant dire que l’usage d’un tel type de données est déjà fortement limité…

Autres problèmes ?

Le problème est qu’à l’approche du mois, beaucoup d’opérations vont donner des résultats erratiques.
Voici quelques exemples (sous PostGreSQL) de requêtes censées retournée la valeur TRUE et qui retournent FALSE !



SELECT CAST('2011-03-31' AS TIMESTAMP)  

     - CAST('1 month' AS INTERVAL)  

     + CAST('1 month' AS INTERVAL) = CAST('2011-03-31' AS TIMESTAMP)

Du fait des nombres de jours fluctuant des mois, l’ajout puis le retrait d’un mois ne donne pas forcément la même date !
(erreur de précision)



SELECT CAST('2011-02-28' AS TIMESTAMP) + CAST('1 month' AS INTERVAL) + CAST('1 day' AS INTERVAL)  

     = CAST('2011-02-28' AS TIMESTAMP) + CAST('1 day' AS INTERVAL)   + CAST('1 month' AS INTERVAL)

Marche presque tout le temps, sauf lorsque la date choisie avoisienne la fin du mois…
(erreur de commutativité)



SELECT CAST('2011-03-31' AS TIMESTAMP) + CAST('2 month' AS INTERVAL) 

     = CAST('2011-03-31' AS TIMESTAMP) + CAST('1 month' AS INTERVAL) + CAST('1 month' AS INTERVAL)

Encore les durées de mois…
(Erreur de répétition)

En conclusion…

Évitez d’utiliser le type INTERVAL pour des durées. Dans l’entreprise il est courant de mesurer des durées à partir d’une granularité simple comme le jour, la minute ou l’heure et sous forme décimale. Par exemple l’heure décimale. Il faut alors ajouter quelques routines de conversion sous forme d’UDF et présenter les données sous forme de vues.



-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

8 réflexions au sujet de « Le type INTERVAL de SQL et ses dangers »

  1. Avatar de SarsaineSarsaine

    Je sais que ça fait longtemps mais juste pour une petite mise au point…

    En C : ((float) ((int) un_float)) == un_float pourra aussi renvoyer false

    Comme l’a dis cinephil : Ce résultat a du sens.
    Le problème est comme bien souvent : Entre la chaise et le clavier…

    Bien à vous.

    ↓
  2. Avatar de cinephilcinephil

    Bien vu ! Je n’avais pas pensé à ça !

    Compte tenu de la manière dont MySQL traite cette opération, la réponse est évidemment : dernier jour du mois de février moins un mois = jour correspondant de janvier = 2011-01-28

    Par contre, la « logique » de MySQL est respectée avec une soustraction simple :
    SELECT CAST( ‘2011-03-30′ AS DATE ) – INTERVAL 1 MONTH = 2011-02-28
    => Le 30 février n’existant pas, MySQL donne le dernier jour du mois de février.

    A+

    ↓
  4. Avatar de cinephilcinephil

    Oui, mais que se passe t-il pour le 30, le 29, le 28 ou le 27 ???

    Euh… je crois l’avoir dit dans mon précédent commentaire non ?
    Chez MySQL, ça donne un résultat logique et pas une date inexistante.

    Et si maintenant je dois faire une différence entre deux dates calculées par ajout/retrait dans une requête ??? => données fausses !!!!

    Euh… tu peux préciser et donner un exemple STP ?

    ↓
  6. Avatar de cinephilcinephil

    Après ta réponse, j’ai relu ton billet et je viens de comprendre la subtilité qui peut entraîner des erreurs. Du coup j’ai fait un petit test rapide sur MySQL…
    Ajouter un mois au 30 janvier 2011 :

    SELECT CAST( '2011-01-30' AS DATE ) + INTERVAL 1 MONTH

    => ‘2011-02-28′
    Comme le 30 février n’existe pas, MySQL s’arrête au dernier jour du mois suivant, le 28.
    Ça fonctionne de manière identique en partant du 29 ou du 31 janvier. Pour une année bissextile, le résultat donne bien le 29 février, y compris pour l’an 2000.
    Le principe est le même pour les autres mois, si l’ajout d’un mois dépasse la capacité du mois, MySQL donne le dernier jour du mois (31 mars + 1 mois = 30 avril).

    Je trouve que ce résultat a du sens. Si je veux ajouter un mois à aujourd’hui, je ne cherche pas à ajouter un nombre de jours mais à trouver le jour équivalent le mois prochain ; si aujourd’hui est inférieur au nombre de jours du mois suivant, je garde le même numéro de jour dans le mois et si aujourd’hui est supérieur ou égal au nombre de jours du mois suivant, je ne souhaite pas sauter au début du mois + 2 donc j’attends bien le dernier jour du mois prochain.

    ↓

Laisser un commentaire