Problem: ein select * from SUCHEN where A_DATE <= (select OTHER_DATE from OTHER_TABLE ) gibt keine Datensätze zurück, obwohl es in der Tabelle SUCHEN Datensätze mit dem gleichen Datum, wie in OTHER_TABLE gibt.
Die Aussage der Kollegen zu dem Thema: „Wenn man in Oracle ein DATE benutzt werden auch Stunden, Minuten und Sekunden abgespeichert und deshalb MUSS man immer TRUNC() benutzen, wenn man mit dem Type Date hantiert. Das gilt auch wenn das Date per setDate über JDBC gesetzt wird“.
Die Aussage hat mich sehr verwirrt, den bis dato hatte ich noch nie ein Problem mit dem Date-Type in Oracle und auch nicht mit entsprechenden Abfragen — also muss ich mal wieder recherchieren, was hier passiert ist.
Als erstes habe ich mich beim Hersteller mit der entsprechenden Dokumentation schlau gelesen:
Database SQL Language Reference > 11g2 > Basic Elements of Oracle SQL > Oracle Build-in Data Types > Date
„Oracle stores the following information: year, month, day, hour, minute, and second.“
Hopsala, das muss man wirklich wissen. Die Wette hätte ich verloren.
Aber wie kann es dann doch funktionieren? Ganz einfach, man muss wissen, was man tut:
JDBC: Bei einem Update/Insert über JDBC muss man sich keine Gedanken machen, denn setDate schneidet die Zeit ab. Eine entsprechende REferenze war schon etwas schwieriger zu finden: Database JDBC Developer’s Guide > 11g2 > JDBC Reference Information > Embedded JDBC Escape Syntax. Dort gibt es eine Note, das es bei der Benutzung von Date Problem mit dem Index geben kann und man eine entsprechende Syntax benutzen muss. Als Begründung steht dort:
„This is because if you use the setDate method, then the time component of the Oracle DATE data will be lost and if you use the setTimestamp method, then the index on the DATE column will not be used.“
Man muss also „nur“ aufpassen, wenn man ein Date per setDate in einem PreparedStatement nutzt und Oracle dann einen Index nutzen soll — soweit so gut.
Plain SQL: Hier muss man aufpassen, wenn man Daten-Typen mischt. Ein leidlich bekanntes Thema, dass bei den heutigen zu meist automatischen Typ-Konvertierungen in Java immer seltener beachtet wird — kein Entwickler will sich mit diesen explizieten Umwandlungen herumschlagen. Beim Date ist das aber extrem wichtig, denn ein Date speichert (wie oben gelernt auch eine Zeit mit ab). Bei einer Zuweisung eines Werts vom Typ Timestamp wird die Zeitkomponente nicht abgeschnitten, sondert wandert mit in den Wert vom Datentyp Date. Bei einem einfach select kann man diesen Unterschied nicht mehr sehen, denn es wird immer nut Jahr-Monat-Tag ausgegeben — die Zeit fällt unter den Tisch.
Interessant wird es erst, wenn man zwei Werte vom Datentyp Date mit einander vergleicht: solange beide Werte korrekt initialisiert wurden, also per JDBC oder im SQL mit einem trunc() ist alles okay. Wird aber ein Wert ohne trunc() also z.b. mit ein einem einfach sysdate initialisiert, dann sind zwei scheinbare Werte vom Type Date nicht mehr gleich.
Also: bei der Zuweisung darauf achten, dass man einer Date Variable immer nur ein Datum mit gibt und keine Zeit (JDBC macht das implizit, bei SQL muss man ggf. Trunc() nutzen)
Kommen wir zurück zum Ausgangs-Select. In der Tabelle SUCHEN wurden in einigen Zeilen die Spalte A_DATE mit der Funktion sysdate zugewiesen, ohne dass der Zeit-Anteil auf 0 gesetzt wurde. Damit wurden genau diese Datensätze nicht mehr gefunden für ein gleiches Datum. Unterumständen hätte auch schon ein einfaches kleiner anstatt des kleiner-gleichs das PRoblem gelöst. Nur muss man dann auf das zweite Datum immer einen Tag drauf rechnen — das scheuen sich viele Programmiere vor.
Ergo: Die Aussage, bei Date muss man immer TRUNC() benutzen ist mal wieder nur die halbe Wahrheit. Viel schlimmer an der Aussage ist, dass man bei einem falschen Einsatz von trunc(), d.h. wenn man die Werte eine Spalte einer Tabelle trunc(), Oracle in das PRoblem läuft, dass es keine Index mehr nutzen kann. (Oracle SQL Tuning with function-based indexes)