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)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 (LUW)
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' DAYPrenez toujours en compte l'utilisation d'une condition d'intervalle explicite lors de la comparaison de dates.
