von Markus Winand.

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.

Hinweis in eigener Sache

Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

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.

LIKE auf Datums-Typen

Die folgende Verschleierung ist besonders tückisch:

sale_date LIKE SYSDATE

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.

Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz