Seltsames Oracle-Datumsverhalten

Question

Ich hatte ein seltsames Problem in einer unserer Produktionsdatenbanken. Lange Rede kurzer Sinn, einfache Abfrage:

select id, trunc(stdate) from table_name where trunc(stdate) = '05-FEB-09';  

lieferte keine Zeilen zurück. Allerdings,

select trunc(stdate) from table_name where id = sought_after_id;

lieferte '05-FEB-09' zurück. Erst als ich versuchte:

update table_name set stdate = '05-FEB-09' where id = sought_after_id;

funktionierte meine originale Abfrage wie erwartet:

select id, trunc(stdate) from table_name where trunc(stdate) = '05-FEB-09';
> sought_after_id, '05-FEB-09'

Also, was war mit meinen stdate-Werten los?

Answer 1

Sie sollten immer Daten mit Daten vergleichen (Äpfel mit Äpfeln...) und nicht auf implizite Konvertierungen verlassen.

Da TRUNC(date) ein Datum zurückgibt, sollten Sie es mit einem Datum vergleichen:

select id, trunc(stdate) from table_name where trunc(stdate) = DATE '2009-02-05'

oder

select id, trunc(stdate) 
  from table_name 
 where trunc(stdate) = TO_DATE('05-FEB-09', 'DD-MON-RR'))

Aktualisierung als Antwort auf Igors ersten Kommentar:

Sich auf die implizite Datenkonvertierung zu verlassen, macht das Ergebnis der Abfrage von mehreren Sitzungsparametern abhängig. Einer dieser Parameter muss geändert worden sein, wenn Sie jetzt verschiedene Ergebnisse sehen als an den Tagen zuvor. Sie können Ihre Abfrage "session-unabhängig" machen, indem Sie nicht auf implizite Konvertierungen verlassen.

Es lohnt sich zu betonen, dass Ihre erste Abfrage von den Parametern der Client-Sitzung abhängt. Wenn die Sitzung ihre Standarddatum Anzeigeeinstellung NLS_DATE_FORMAT ändert, wird Ihre Abfrage nicht die gleichen Ergebnisse zurückgeben.

In ähnlicher Weise ist DD-MON-RR ein vollkommen akzeptables Datumsformat für Display, aber es ist nicht gut geeignet für die Verwendung in Ihrem Code, da es eine Mehrdeutigkeit hinsichtlich des Jahrhunderts gibt und Sie auf den Parameter NLS_DATE_LANGUAGE für die Monate verlassen.

Answer 2

Es könnte zwei Dinge sein. Erstens könnte die YY Datumsmaske unterschiedliche Werte für das Jahrhundert verbergen; wenn wir keines angeben mit YYYY, wird es auf das aktuelle Jahrhundert zurückgesetzt. Zweitens enthalten Oracle-Daten ein Zeit-Element. Wenn wir keine Zeit angeben, wird standardmäßig Mitternacht verwendet.

SQL> select * from d
  2  /

D1                ID
--------- ----------
19-JAN-10          1
19-JAN-10          3
19-JAN-10          2

SQL> select * from d
  2  where d1 = to_date('19-JAN-10', 'DD-MON-YY')
  3  /

D1                ID
--------- ----------
19-JAN-10          3

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI-SS'
  2  /

Session geändert.

SQL> select * from d
  2  /

D1                           ID
-------------------- ----------
19-JAN-1910 00:00-00          1
19-JAN-2010 00:00-00          3
19-JAN-2010 12:00-00          2

SQL>

Nur ein Datensatz - #3 - stimmte mit to_date('19-JAN-10', 'DD-MON-YY') überein, weil Nr. 1 ein anderes Jahrhundert hatte und Nr. 2 eine andere Uhrzeit hatte.

Bearbeiten

Alle Zeilen in meiner Tabelle haben das gleiche Jahrhundert und ein 00:00-00-Zeitelement.

Nun, das stimmt jetzt, aber vielleicht nur, weil Sie die Daten korrigiert haben. Es gab definitiv etwas Besonderes an diesem bestimmten Datum, sonst hätte Oracle es genauso behandelt wie alle anderen.

Durch das Ausführen des Updates haben Sie die Unterschiede gelöscht. Ex post facto ist es uns nicht möglich zu sagen, was mit den Daten nicht stimmte. Wenn Sie eine Überwachungsprotokoll haben, sollten Sie es konsultieren, da es hilfreich wäre zu wissen, welcher Teil Ihres Systems diesen Datensatz mit einer ungültigen Datumsüberlagerung eingefügt oder aktualisiert hat, damit Sie ihn korrigieren können.