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.

Für alle Anwendungsentwickler […] sollte der schmale Band […] eine Pflichtlektüre sein — ADMIN-Magazin

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.

DB2
CREATE FUNCTION quarter_begin(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q');
CREATE FUNCTION quarter_end(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q') + 3 MONTHS - 1 SECOND;
MySQL
CREATE FUNCTION quarter_begin(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN CONVERT
       (
         CONCAT
         ( CONVERT(YEAR(dt),CHAR(4))
         , '-'
         , CONVERT(QUARTER(dt)*3-2,CHAR(2))
         , '-01'
         )
       , datetime
       );
CREATE FUNCTION quarter_end(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD
       ( DATE_ADD ( quarter_begin(dt), INTERVAL 3 MONTH )
       , INTERVAL -1 MICROSECOND);
Oracle
CREATE FUNCTION quarter_begin(dt IN DATE) 
RETURN DATE
AS
BEGIN
   RETURN TRUNC(dt, 'Q');
END;
/
CREATE FUNCTION quarter_end(dt IN DATE) 
RETURN DATE
AS
BEGIN
   -- the Oracle DATE type has seconds resolution
   -- subtract one second from the first 
   -- day of the following quarter
   RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q') 
        - (1/(24*60*60));
END;
/
PostgreSQL
CREATE FUNCTION quarter_begin(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
    RETURN date_trunc('quarter', dt);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION quarter_end(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 quarter_begin (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0)  
END;
CREATE FUNCTION quarter_end (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD
         ( ms
         , -3 
         , DATEADD(mm, 3, dbo.quarter_begin(@dt))
         );
END;

Ä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.

Tipp

Formuliere Suchen nach zusammenhängenden Perioden als explizite Bereichsbedingung. Auch, wenn es ein einzelner Tag ist – z. B. bei der Oracle Datenbank:

    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.

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

LIKE auf Datums-Typen

Die folgende Verschleierung ist besonders tückisch:

sale_date LIKE SYSDATE

Diese Bedingung scheint auf den ersten Blick nicht verschleiert zu sein, weil sie keine Funktion auf der Tabellenspalte verwendet.

Durch die Verwendung des LIKE-Operators wird aber ein String-Vergleich erzwungen. Je nach Datenbank führt das entweder zu einem Fehler oder zu einer impliziten Typen-Kovertierung beider Seiten. Der Predicate-Information-Bereich des Ausführungsplanes zeigt, was die Oracle Datenbank macht:

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

Die Funktion INTERNAL_FUNCTION führt die Konvertierung auf der Tabellenspalte durch. Sie verhindert aber, wie jede andere Funktion auch, dass ein Index auf SALE_DATE benutzt wird.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.