SECOND
ohne BuchteileSECOND
ohne Buchteile. Verwende SECOND_MICROSECOND
interval
nichtdate(<timestamp>)
time(<timestamp>)
Ein häufiger Fehler liegt in der Verwendung von Stringformatierungsfunktionen (z. B. to_char
) anstatt extract
um einzelne Datums- oder Zeitfelder zu extrahieren. Diese Funktionen wenden oft unerwartete Formatierungsregeln an: z. B. führende Leerzeichen oder Nullen oder Punkt (.
) statt Komma (,
) als Dezimaltrennzeichen abhängig von der aktuellen Umgebung (Locale).
Dieses umgebungsabhängige Verhalten kann zu Fehlern führen, die sich nicht überall äußern und daher schwer zu beheben sind.
Where
-KlauselBetrachte das folgende Anti-Pattern:
WHERE EXTRACT(YEAR FROM some_date) = 2016
Dieses Anti-Pattern wird oft genutzt, damit man den „letzten Moment“ des relevanten Zeitbereiches nicht explizit angeben muss. Es ist tatsächlich ein wichtiges und erstrebenswertes Ziel, da der „letzte Moment“ eines Zeitbereiches nicht immer ermittelt werden kann:
Es ist allgemein bekannt, dass die verschiedenen Monate verschiedene Längen haben. Selbst die Regeln für Schaltjahre sind zumindest teilweise bekannt. Soweit kann man den „letzten Moment“ also algorithmisch ermitteln.
Da gibt es aber auch noch die Schaltsekunden, die unregelmäßig sind. Sie werden gelegentlich (ca. alle 18 Monate) bei Bedarf eingefügt. Die letzte Sekunde des Jahres 2016 war in UTC zum Beispiel 23:59:60 (oder 00:59:60 des 1. Januar 2017 in CET). Wenn man davon ausgeht, dass ein Jahr um 23:59:59 endet, kann man also eine Sekunde verpassen.3
Da die Schaltsekunden unregelmäßig sind und verhältnismäßig kurzfristig angekündigt werden – die Schaltsekunde 2016 wurde weniger als sechs Monate vorher angekündigt –, ist es im allgemeinen Fall unmöglich den „letzten Moment“ für mehr als sechs Monate vorauszuberechnen.
Neben diesem, mehr oder weniger theoretischem Sonderfall, ist die Berechnung des „letzten Momentes“ natürlich auch mühsam und schon alleine deswegen vermeidenswert.
Selbst wenn man den letzten Tag und die letzte Sekunde einer Zeitspanne korrekt ermittelt, muss man noch immer genügend Nachkommastellen angeben, um den „letzten Moment“ korrekt anzugeben. Wenn man weiß, dass der entsprechende Datentyp keine Nachkommastellen zulässt (z. B. timestamp(0)
), muss man natürlich keine Nachkommastellen angeben. Wenn der Datentyp später jedoch geändert wird (z. B. auf timestamp(6)
), wird kaum jemand nach den „letzter Moment“-Annahmen suchen und gegebenenfalls die nötige Anzahl an 9-ern hinzufügen.
Es ist daher generell gute Praxis ohne Angabe des „letzten Moment“ eines Zeitbereichs auszukommen. Extract
, cast
und Stringformatierung in der where
-Klausel sind aber meist der falsche Weg zum richtigen Ziel.
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 folgende where
-Klausel ist gleichwertig zu dem extract
-Beispiel von oben, vermeidet es aber noch immer den „letzten Moment“ des Jahres 2016 angeben zu müssen:
WHERE some_date >= DATE'2016-01-01'
AND some_date < DATE'2017-01-01'
Beachte das Muster: Verwende eine inklusive Bedingung (>=
) für die Untergrenze, aber eine exklusive Bedingung (<
) für die Obergrenze. Als Obergrenze muss man daher den ersten Moment angeben, der ausgeschlossen werden soll. Das inklusive/exklusive-Muster braucht den „letzten Moment“ nicht, weil es stattdessen den weniger problematischen ersten Moment zweimal verwendet.
Beachte, dass SQL’s between
beide Grenzwerte inkludiert und daher nicht für das inklusive/exklusive-Muster genutzt werden kann.
Im Vergleich zur extract
-Lösung hat die inklusive/exklusive-Bedingung zwei Vorteile:
Ein einzelner Tag, Monat, … kann einfach abgegrenzt werden – selbst wenn der Zeitbereich nicht auf einen Kalendertag, -monat, … ausgerichtet ist.
Zur Verdeutlichung versuche die folgende where
-Klausel mittle extract
, to_char
oder Ähnlichem umzusetzen:4
WHERE some_date >= DATE'1994-03-11'
AND some_date < DATE'1995-03-11'
Ein Index auf some_date
ist kaum nützlich, wenn diese Spalte in der where
-Klausel nur in einem Ausdruck wie extract
auftaucht.5Das inklusive/exklusive-Muster kann diesen Index problemlos nutzen. Mehr über Indizierung auf Use The Index, Luke!
Einige Datenbanken bieten zusätzliche extract
-Felder an. In der folgenden Tabelle sind die verbreitetsten aufgelistet. Beachte dabei jedoch, dass es sich dabei um Erweiterungen der Hersteller handelt: Sie können sich also bei verschiedenen Datenbaken auch unterschiedlich verhalten. Das Feld week
funktioniert zwar in drei getesteten Datenbanken, liefert aber bei jeder ein anderes Ergebnis.
extract(isoyear)
um das zugehörige Jahr zu erhaltenextract(isoyear)
um das zugehörige Jahr zu erhalten12.3456789
sekunden ergibt 123456789
Praktisch alle Datenbanken bieten eine Möglichkeit einzelnen Komponenten aus Datums- oder Zeit-Wertes (date
, time
, timestamp
) zu extrahieren. Für jene Fälle, bei denen der Standard-Extract
-Ausdruck nicht funktioniert, folgt eine Beschreibung der entsprechenden proprietären Alternativen.
datepart
: SQL ServerMicrosoft SQL Server bietet die proprietäre Funktion datepart
an. Das folgende Beispiel ist äquivalent zu extract(year from <datetime>)
.
DATEPART(year, <datetime>)
Der Ergebniswert ist immer vom Type integer
. Die Sekundenbruchteile kann man über gesonderte Feldnamen extrahieren (z. B. millisecond
).
Der folgende Ausdruck ist für bis zu 9 Nachkommastellen gleichwertig zu extract(second from <datetime>):
DATEPART(second , <datetime>)
+ CAST(DATEPART(nanosecond, <datetime>)
AS NUMERIC(9,0)
)/power(10,9)
Siehe „DATEPART (Transact-SQL)“ für eine Liste aller verfügbaren Feldnamen.
SQLite bietet zur Formatierung von Datums- und Zeitangaben die proprietäre Funktion strftime
an.6 Um ein einzelnes Feld zu extrahieren formatiert man dieses Feld als Zeichenkette und führt nötigenfalls eine Typenkonvertierung (cast
) auf einen numerischen Datentyp durch.
Das folgende Beispiel setzt extract(year from <datetime>)
in SQLite um:
CAST(STRFTIME ('%Y', <datetime>) AS NUMERIC)
Beachte, dass der Formatstring '%S'
(für Sekunden) keine Nachkommastellen liefert. Verwende stattdessen '%f'
(Sekunden mit drei Nachkommastellen):
CAST(STRFTIME ('%f', <datetime>) AS NUMERIC)
MySQL’s und MariaDB’s extract
liefern nur ganzzahlige Werte. Um die Sekunden mit Bruchteilen zu erhalten, kann man das proprietäre extract
Feld second_microsecond
verwenden:
EXTRACT(second_microsecond FROM <datetime>)/power(10,6)
20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.
Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how
SQL:2016, Part 2, §6.30, Syntax Rule 7
SQL:2016, Part 2, §6.13, General Rule 14c-d für date
, SQL:2016, Part 2, §6.13, General Rule 16d-e und 17d-e für time
.
Der Oracle-Type date
unterstützt jedoch weder Sekundenbruchteile, noch Zeitzonen.
Durch die Zeitzonen verschiebt sich die Schaltsekunde natürlich. Allgemein gesagt endet also nicht jede Minute mit der 59. Sekunde.
Die Zeit, in der Douglas Noel Adams 42 Jahre alt war.
Diese Index-Spalte ist „kaum“ nützlich, weil die Datenbank diese Spalte noch immer als Index-Filterprädikate nutzen kann (im Extremfall bei einem Full-Index-Scan). Im Vergleich zu einem Zugriffsprädikate ist das jedoch „kaum“ nützlich.
„Proprietäre“ bezieht sich hierbei auf die Sicht des SQL-Standards. strftime
ist dennoch standardisiert – nicht von SQL, aber von POSIX.