par Guillaume Lelarge.

Types date


La plupart de ces pratiques impliquent les types DATE. La base de données Oracle est particulièrement vulnérable car elle n'a qu'un type DATE qui inclut toujours en plus un composant horaire.

Une pratique fréquente revient à utiliser la fonction TRUNC pour supprimer le composant horaire. En vérité, cela ne le supprime pas, cela le configure plutôt à minuit car la base de données Oracle n'a pas de vrai type DATE. Pour supprimer le composant horaire pour une recherche, vous pouvez utiliser la fonction TRUNC des deux côtés d'une comparaison, par exemple pour rechercher les ventes de la veille :

SELECT ...
  FROM ventes
 WHERE TRUNC(date_vente) = TRUNC(sysdate - INTERVAL '1' DAY)

Cette requête est parfaitement valide et correcte mais elle ne peut pas utiliser un index sur DATE_VENTE. Cela correspond à l'explication dans « Recherche insensible à la casse en utilisant UPPER ou LOWER » ; TRUNC(date_vente) est complètement différent de DATE_VENTE. Les fonctions sont des boîtes noires pour la base de données.

Il existe une solution assez simple pour ce problème : un index fonctionnel.

CREATE INDEX nom_index
          ON ventes (TRUNC(date_vente))

Mais du coup, vous devez toujours utiliser TRUNC(date_vente) dans la clause where. Si ce n'est pas le cas, parfois avec, parfois sans TRUNC, vous aurez alors besoin de deux index !

Le problème survient aussi avec les bases de données qui ont des types de date purs si vous recherchez une période plus longue comme le montre la requête MySQL suivante :

SELECT ...
  FROM ventes
 WHERE DATE_FORMAT(date_vente, "%Y-%M")
     = DATE_FORMAT(now()     , "%Y-%M')

La requête utilise un format de date qui contient seulement l'année et le mois : encore une fois, cette requête est tout à fait correcte mais elle a le même problème que précédemment. Néanmoins, la solution ci-dessus ne s'applique pas à MySQL avant la version 5.7 parce que MySQL ne supporte pas les index fonctionnels avant cette version.

L'alternative revient à utiliser une condition d'intervalle explicite. Voici une solution générique qui fonctionne pour toutes les bases de données :

SELECT ...
  FROM ventes
 WHERE date_vente BETWEEN DEB_TRIMESTRE(?) 
                      AND FIN_TRIMESTRE(?)

Si vous avez fait vos devoirs, vous reconnaissez probablement la signature de l'exercice sur les employés de 42 ans.

Un index sur DATE_VENTE est suffisant pour optimiser cette requête. Les fonctions DEB_TRIMESTRE et FIN_TRIMESTRE calculent les dates limites. Le calcul peut devenir un peu complexe car l'opérateur between inclut toujours les valeurs limites. Du coup, la fonction FIN_TRIMESTRE doit renvoyer un horodatage juste avant le premier jour du prochain trimestre si DATE_VENTE a une composante horaire. Cette logique peut se cacher dans la fonction.

Les exemples suivants montrent l'implémentation des fonctions DEB_TRIMESTRE et FIN_TRIMESTRE pour les différentes bases de données.

DB2
CREATE FUNCTION deb_trimestre(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q');

CREATE FUNCTION fin_trimestre(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q') + 3 MONTHS - 1 SECOND;
MySQL
CREATE FUNCTION deb_trimestre(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN CONVERT
       (
         CONCAT
         ( CONVERT(YEAR(dt),CHAR(4))
         , '-'
         , CONVERT(QUARTER(dt)*3-2,CHAR(2))
         , '-01'
         )
       , datetime
       );

CREATE FUNCTION fin_trimestre(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD
       ( DATE_ADD ( deb_trimestre(dt), INTERVAL 3 MONTH )
       , INTERVAL -1 MICROSECOND);
Oracle
CREATE FUNCTION deb_trimestre(dt IN DATE) 
RETURN DATE
AS
BEGIN
   RETURN TRUNC(dt, 'Q');
END;
/

CREATE FUNCTION fin_trimestre(dt IN DATE) 
RETURN DATE
AS
BEGIN
   -- le type DATE Oracle a une résolution à la seconde
   -- soustraire une seconde à la première date du
   -- trimestre suivant
   RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q') 
        - (1/(24*60*60));
END;
/
PostgreSQL
CREATE FUNCTION deb_trimestre(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
    RETURN date_trunc('quarter', dt);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION fin_trimestre(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
   RETURN   date_trunc('quarter', dt) 
          + interval '3 month'
          - interval '1 microsecond';
END;
$$ LANGUAGE plpgsql;
SQL Server
CREATE FUNCTION deb_trimestre (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0)  
END
GO

CREATE FUNCTION fin_trimestre (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD
         ( ms
         , -3 
         , DATEADD(mm, 3, dbo.deb_trimestre(@dt))
         );
END
GO

Vous pouvez utiliser des fonctions auxiliaires similaires pour les autres périodes. La plupart seront moins complexes que les exemples ci-dessus, tout spécialement lors de l'utilisation des conditions supérieur ou égal (>=) ou plus petit que (<) à la place de l'opérateur between. Bien sûr, vous pourriez calculer les dates limites dans votre application si vous le souhaitez.

Astuce

Écrivez des périodes continues pour des conditions d'intervalle explicites. Faites-le même pour un seul jour. Par exemple, avec la base de données Oracle :

    date_vente >= TRUNC(sysdate)
AND date_vente <  TRUNC(sysdate + INTERVAL '1' DAY)

Un autre problème fréquent est de comparer les dates en tant que chaînes de caractères, comme indiqué dans l'exemple PostgreSQL suivant:

SELECT ...
  FROM ventes
 WHERE TO_CHAR(date_vente, 'YYYY-MM-DD') = '1970-01-01'

Encore une fois, le problème réside en la conversion de la colonne date_vente. Ce genre de conditions survient souvent de la croyance que vous ne pouvez pas passer d'autres types que des nombres et des chaînes à la base de données. Néanmoins, les paramètres liés supportent tous les types de données. Cela signifie par exemple que vous pouvez utiliser un objet java.util.Date comme paramètre lié. C'est un autre bénéfice qu'apportent les paramètres liés.

Si vous ne pouvez pas le faire, vous devez seulement convertir le terme de recherche plutôt que la colonne de la table:

SELECT ...
  FROM ventes
 WHERE date_vente = TO_DATE('1970-01-01', 'YYYY-MM-DD')

Cette requête peut utiliser un index simple sur DATE_VENTE. De plus, elle convertit la chaîne en entrée une seule fois. La requête précédente doit convertir toutes les dates stockées dans la table avant de pouvoir les comparer avec le terme de recherche.

Quelle que soit la modification que vous faites, en utilisant un paramètre lié ou en convertissant l'autre côté de la comparaison, vous pouvez facilement introduire un bug si DATE_VENTE a une composante horaire. Vous devez utiliser une condition d'intervalle explicite dans ce cas :

SELECT ...
  FROM ventes
 WHERE date_vente >= TO_DATE('1970-01-01', 'YYYY-MM-DD') 
   AND date_vente <  TO_DATE('1970-01-01', 'YYYY-MM-DD') 
                   + INTERVAL '1' DAY

Prenez toujours en compte l'utilisation d'une condition d'intervalle explicite lors de la comparaison de dates.

LIKE sur les types date

Le problème suivant est assez vicieux :

date_vente LIKE SYSDATE

Cela ne semble pas poser problème au premier coup d'œil car aucune fonction n'est utilisée.

Néanmoins, l'opérateur LIKE force une comparaison de chaîne de caractères. Suivant la base de données, cela peut amener une erreur ou forcer une conversion implicite de type des deux côtés. La section « Predicate Information » du plan d'exécution montre ce que fait la base de données Oracle :

filter( INTERNAL_FUNCTION(DATE_VENTE)
   LIKE TO_CHAR(SYSDATE@!))
La fonction INTERNAL_FUNCTION convertit le type de la colonne DATE_VENTE. Le deuxième effet de bord est l'impossibilité d'utiliser un index simple sur COLONNE_DATE comme avec toute autre fonction.

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license