Datums-Typen

Eine der häufigsten Verschleierung betrifft Datums-Spalten. Die Oracle Datenbank ist dafür besonders anfällig, weil sie nur den DATE-Typen hat, der immer eine Uhrzeit enthält.

Um den Zeitanteil aus einer DATE-Spalte zu entfernen, hat sich die TRUNC-Funktion durchgesetzt. Die Zeit wird aber nicht wirklich abgeschnitten, sondern auf Mitternacht gesetzt – schließlich gibt es bei der Oracle Datenbank keinen Datums-Typen ohne Zeit. Wenn man diese Funktion auf beiden Seiten eines Vergleiches verwendet, kann man nach einem Datum suchen, ohne die Zeit zu berücksichtigen:

SELECT ...
  FROM sales
 WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)

Die Abfrage ist absolut korrekt, kann aber einen Index auf SALE_DATE nicht ordentlich nutzen. Wie schon zuvor in „Groß- und Kleinschreibung mit UPPER oder LOWER ignorieren“ erklärt; TRUNC(sale_date) ist etwas völlig anders als SALE_DATE – zumindest für die Datenbank. Denke an eine Blackbox.

Die einfachste Lösung ist natürlich ein Funktions-basierter Index:

CREATE INDEX index_name
          ON sales (TRUNC(sale_date))

Dann muss die Spalte SALE_DATE in der where-Klausel immer mit TRUNC verwenden. Wenn die Bedingung aber uneinheitlich formuliert ist – manch­mal mit, manchmal ohne TRUNC – benötigt man zwei Indizes.

Das Problem tritt aber auch bei Datenbanken auf, die einen reinen Datums-Typen haben. Wenn man zum Beispiel alle Einträge für einen Monat sucht, wie bei dieser MySQL-Abfrage:

SELECT ...
  FROM sales
 WHERE DATE_FORMAT(sale_date, "%Y-%M")
     = DATE_FORMAT(now()    , "%Y-%M")

Die Bedingung formatiert das Datum auf beiden Seiten als Text, der nur Jahr und Monat enthält. Wiederum eine völlig korrekte Abfrage, die dasselbe Problem wie die TRUNC-Abfrage hat. Die Lösung von oben kann funktioniert bei MySQL aber erst ab Version 5.7, da MySQL davon keine Funktions-basierte Indizierung unterstützte.

Es gibt aber eine generische Lösung, die bei allen Datenbanken funktioniert. Dafür muss man die Bedingung als explizite Bereichsbedingung umfor­mu­lieren:

SELECT ...
  FROM sales
 WHERE sale_date BETWEEN quarter_begin(?) 
                     AND quarter_end(?)

Wenn du die Denksport-Aufgabe gemacht hast, bei der man alle 42 Jahre alten Mitarbeiter sucht, erkennst du das Muster vielleicht.

Bei dieser Methode genügt ein einfacher Index auf der Spalte SALE_DATE. Die beiden Funktionen QUATER_BEGIN und QUARTER_END berechnen das jeweilige Grenz-Datum. Diese Berechnung kann durchaus komplex werden, da der between-Operator die Grenz-Werte immer inkludiert. Denn wenn die Spalte SALE_DATE eine Zeit-Komponente enthält, muss die Funktion QUARTER_END eine Zeit liefern, die unmittelbar vor dem ersten Tag des nächsten Quartals liegt. Diese Logik kann man ganz gut in den Funktionen verstecken.

Die folgenden Beispiele setzen die Funktionen QUARTER_BEGIN und QUARTER_END für verschiedene Datenbanken um.

Ähnliche Hilfsfunktionen kann man natürlich auch für andere Perioden nutzen. Die meisten sind sogar einfacher als die oben gezeigten. Insbe­son­dere, wenn man anstatt des between-Operators zwei Bedingungen mit größer-gleich (>=) und kleiner (<) verwendet. Natürlich kann man die Be­rech­nung der Grenz-Werte auch in der Applikation durchführen.

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

Eine andere häufige Verschleierung ist, das Datum als Text zu vergleichen – wie im folgenden PostrgeSQL-Beispiel:

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

Das Problem ist wieder die Konvertierung der Spalte SALE_DATE. Solche Bedingungen entstehen oft im Glauben, dass man einer Datenbank nur Zahlen und Texte übergeben kann. Mit Bind-Parametern kann man aber auch andere Daten-Typen verwenden. Das heißt, man kann zum Beispiel ein java.util.Date-Objekt direkt als Bind-Paramter an die Datenbank übergeben. Ein weiterer Vorteil von Bind-Parametern.

Falls das nicht möglich ist, sollte man nicht die Tabellenspalte, sondern den Suchbegriff konvertieren:

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

Damit kann ein Index auf SALE_DATE benutzt werden. Ein zusätzlicher Vorteil ist, dass die Konvertierung nur einmal stattfindet. Bei der vorherigen Abfrage müssen alle Daten konvertiert werden, bevor sie mit dem angegebenen Text verglichen werden können.

Egal wie man das Problem behebt – mit einem Bind-Parameter oder mit TO_DATE – muss man aufpassen, dass sich kein Fehler einschleicht. Wenn die Spalte SALE_DATE eine Zeit-Komponente beinhaltet – wie es bei der Oracle Datenbank zwangsläufig der Fall ist – muss man eine explizite Bereichsbedingung verwenden:

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

Man sollte bei jeder Suche mit einem Datums-Typen an eine explizite Bereichsbedingung denken.

sale_date LIKE SYSDATE

filter( INTERNAL_FUNCTION(SALE_DATE)
   LIKE TO_CHAR(SYSDATE@!))