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 – manchmal 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 umformulieren:
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. Insbesondere, wenn man anstatt des between
-Operators zwei Bedingungen mit größer-gleich (>=
) und kleiner (<
) verwendet. Natürlich kann man die Berechnung 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.