1
votes

La requête SQL to_char BETWEEN renvoie des résultats erronés avec les bons

Ma requête est la suivante:

select * from session ses
inner join event evt
on ses.evt_id = ses.id
inner join application app
on app.id = ses.app_id
where app.id = 9999
and ses.usernumber = 69
and to_char(ses_date, 'dd.MM.yyyy HH24:MI:SS') between '22.10.2015 23:59:59' and '23.10.2015 23:59:59'

Désormais, les premiers résultats datent bien du 23.10.2015 mais il y en a aussi peu du 23.06.2014 .

Comment est-ce possible?

sql datetime oracle

1 commentaires

La comparaison de chaînes de caractères fonctionne de cette façon. Passez au type de données d'horodatage approprié, ou au moins au format ANSI / ISO «2015-10-22 23:59:59».

3 Réponses :

4
votes

Pourquoi convertir cette date en chaîne en premier lieu? C'est inefficace et dangereux. Au lieu de cela, vous pouvez filtrer la colonne directement par rapport aux dates ou aux horodatages littéraux.

Je suppose que vous voulez:

ses_date between to_date('22.10.2015T23:59:59', 'dd.mm.yyyy"T"hh24:mi:ss')
             and to_date('23.10.2015T23:59:59', 'dd.mm.yyyy"T"hh24:mi:ss')

Cela amène des dates dont la portion journalière est 2015, 22 ou 23 octobre.

Bien sûr, vous pouvez également utiliser des horodatages littéraux si vous le souhaitez. Si vous souhaitez reproduire la logique de votre code d'origine:

ses_date >= timestamp '2015-10-22 23:59:59' and ses_date <= timestamp '2015-10-23 23:59:59'

Si vous recevez les paramètres de date sous forme de chaînes en premier lieu, convertissez-les en dates:

ses_date >= date '2015-10-22' and ses_date < date '2015-10-24'

5 commentaires

J'ai besoin d'utiliser des chaînes, car j'analyse la date à partir d'une URL à laquelle un utilisateur tape "& start = 22.10.2015T23: 59: 59 & end = 23.10.2020T23: 59: 59"

@willberthos: puis convertissez les chaînes en date. Voir ma mise à jour.

Je pense que c'est la bonne réponse, mais comme j'utilise JPQL, je ne peux pas utiliser la fonction to_date. Je n'ai pas encore trouvé le moyen de convertir String au bon format JPQL, à partir duquel j'ai posé une question distincte: stackoverflow.com/questions/64857100/...

@willberthos: pourquoi pas simplement: ses_date between to_date(?1, 'dd.mm.yyyy"T"hh24:mi:ss') and to_date(?2, 'dd.mm.yyyy"T"hh24:mi:ss')

car JPQL n'a pas de fonction "to_date"

4
votes

Comment est-ce possible?
Vous utilisez TO_CHAR sur un type de données date pour le convertir en type de données chaîne, puis le filtre est:
AND ses_date >= DATE '2015-10-23'
AND ses_date <  DATE '2015-10-24'
Ainsi, l'analyseur SQL effectue une comparaison alphanumérique des valeurs et la limite inférieure commence le 22 et le 23.06.2014 est supérieure car les premiers caractères de la chaîne sont identiques, puis le deuxième caractère est comparé et 2 < 3 donc '22.10.2015 23:59:59' < '23.06.2014' par rapport à une chaîne. De même, la limite supérieure commence par 23.1 et les 3 premiers caractères sont les mêmes, puis 0 < 1 pour le 4ème caractère donc '23.06.2014' < '23.10.2015 23:59:59' (encore une fois, par rapport à une chaîne ). Ainsi, le filtre renvoie true car la valeur de la chaîne se situe entre les limites supérieure et inférieure.
Comment résoudre ceci:
Ne comparez pas les valeurs sous forme de chaînes; comparez-les comme des dates.
AND ses_date BETWEEN TO_DATE( '22.10.2015 23:59:59', 'DD.MM.YYYY HH24:MI:SS' )
             AND     TO_DATE( '23.10.2015 23:59:59', 'DD.MM.YYYY HH24:MI:SS' )
ou
AND ses_date BETWEEN TIMESTAMP '2015-10-22 23:59:59'
             AND     TIMESTAMP '2015-10-23 23:59:59'
ou
AND ses_date BETWEEN DATE '2015-10-22' + INTERVAL '23:59:59' HOUR TO SECOND
             AND     DATE '2015-10-23' + INTERVAL '23:59:59' HOUR TO SECOND
(Remarque: ceux-ci 2015-10-22 23:59:59 valeurs à partir du 2015-10-22 23:59:59 .)
Si vous ne voulez que des valeurs à tout moment sur le 2015-10-23 alors:
{string value} BETWEEN {string value} AND {string value}

0 commentaires

1
votes

Vous vous attendez à ce que vos valeurs soient évaluées comme des dates, où la composante année est la plus importante, qui se trouve à la fin de votre description de date. Cependant, étant donné que vous convertissez vos dates en chaînes, elles sont évaluées par ordre alphabétique, ce qui signifie que dans la comparaison, la première différence (de gauche à droite) décide du résultat. Alphabétiquement

22.10.2015 23:59:59 <23.06.2014 <23.10.2015 23:59:59

est vrai.

Preuve

Les trois chaînes commencent par un «2». Le premier continue avec un autre «2», tandis que le second continue avec un «3». Le premier est donc plus petit que le second. Le troisième continue avec un «3», tout comme le second. Le deuxième et le troisième continuent avec un «.». Et maintenant vient la différence: le premier continue avec «0» et le troisième continue avec un «1». Même si ces chaînes représentent des dates pour vous et que la composante année du troisième est plus petite que la seconde, en tant que chaînes, la seconde est plus petite que la troisième.

Comment résoudre ça?

Plusieurs approches peuvent être utilisées.

Convertissez votre entrée que vous filtrez à ce jour

Fondamentalement, si vous ne convertissez pas vos dates en chaîne, mais vos entrées to_date , comme @GMB l'a décrit, cela devrait fonctionner correctement.

Notation de date hongroise

Même si la première approche devrait fonctionner hors de la boîte et est une solution parfaitement faisable, si, pour une raison quelconque, ce n'est pas une option (par exemple, les dates dans l'URL peuvent être mal formées en raison de recherches d'utilisateurs), alors vous voudrez peut-être utiliser le format aaaa.mm.jj dans votre URL et utilisez ce format dans vos conversions to_char . L'idée sous-jacente est que si le composant année vient en premier, suivi du mois puis du composant de date, la comparaison entre deux valeurs aura des résultats similaires, indépendamment du fait que vous compariez les valeurs sous forme de dates ou de chaînes.

0 commentaires