SQL extract
ermöglicht den Zugriff auf einzelne Komponenten eines temporalen Datentyps (date
, time
, timestamp
, interval
).
SQL extract
verwendet das Schlüsselwort from
, um den Namen der Zeit-Komponente vom Wert zu trennen:
EXTRACT(<Feld> FROM <Ausdruck>)
Dabei sind die Feldnamen ebenfalls SQL Schlüsselworte – man darf sie also weder in einfache, noch doppelte Hochkommas setzen.
SQL extract
liefert einen exakten numerischen Wert. Wenn man das Feld second
extrahiert, kann der Wert auch Nachkommastellen enthalten.0 Die folgende Tabelle zeigt die Feldnamen, die der SQL-Standard vorsieht.
Bedeutung | extract Feldname |
---|---|
Jahr | YEAR |
Monat | MONTH |
Tag des Monates | DAY |
Stunde (0-23) | HOUR |
Minute | MINUTE |
Sekunden (inklusive Bruchteil) | SECOND |
Zeitzone: Stunde | TIMEZONE_HOUR |
Zeitzone: Minute | TIMEZONE_MINUTE |
Verwandte Funktionen
Extract
kann jeweils nur ein einzelnes Feld extrahieren. Um ein vollständiges Datum (Jahr, Monat, Tag) oder eine vollständige Zeit (Stunde, Minute, Sekunde) aus einem timestamp
-Wert zu extrahieren, kann man cast
nutzen:1
CAST(<timestamp> AS [DATE|TIME])
Das ist insbesondere in der group by
-Klausel nützlich. In der where
-Klausel sollte man jedoch vorsichtig sein: Siehe „Unangebrachte Verwendung in der Where
-Klausel“ unten.
Vorsicht: Oracle-Datenbank
Die Oracle-Datenbank hat keinen vordefinierten Datumstypen ohne Zeitangabe. Selbst der Oracle-Datentyp date
hat die Zeitkomponenten – Oracle-Datentyp date
entspricht in dieser Hinsicht dem Standard-Typen timestamp
.2
Eine Typenkonvertierung (cast
) auf date
verwirft die Zeitkomponente bei der Oracle-Datenbank daher nicht.
Um nur das Datum – ohne Zeitkomponente – zu berücksichtigen, wird bei der Oracle-Datenbank häufig auf die proprietäre Funktion trunc
zurückgegriffen, um die Zeitfelder auf null (0
) zu setzen:
trunc(<timestamp>)
Beachte, dass das Ergebnis noch immer Zeitkomponenten hat – sie sind einfach nur null (0
). Der Effekt ist im Wesentlichen so, wie das folgendes Standard-SQL cast
:
CAST(CAST(<timestamp> AS DATE) AS TIMESTAMP)
Kompatibilität
SQL extract
gab es schon in SQL-92 (intermediate) und ist nun Teil des optionalen Features F052, “Intervals and datetime arithmetic”. Trotz des Alters und der Relevanz wird extract
noch nicht von allen gängigen Datenbanken unterstützt.
Häufige Fehler
Stringformatierung
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.
Unangebrachte Verwendung in der Where
-Klausel
Betrachte 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:
- Zeiteinheiten sind uneinheitlich
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.
- Die Auflösung der Zeitangabe ist unbekannt (zumindest in der Zukunft)
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. auftimestamp(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.
Hinweis in eigener Sache
Ich lebe von SQL-Schulungen, anderen SQL-Dienstleistungen und dem Verkauf meines Buches. Mehr dazu auf winand.at.
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:
- Auf alle Zeitbereiche anwendbar
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 mittleextract
,to_char
oder Ähnlichem umzusetzen:4WHERE some_date >= DATE'1994-03-11' AND some_date < DATE'1995-03-11'
- Einfacher Indizierbar
Ein Index auf
some_date
ist kaum nützlich, wenn diese Spalte in derwhere
-Klausel nur in einem Ausdruck wieextract
auftaucht.5Das inklusive/exklusive-Muster kann diesen Index problemlos nutzen. Mehr über Indizierung auf Use The Index, Luke!
Proprietäre Erweiterung: zusätzliche Felder
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.
Proprietäre Alternativen
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 Server
Microsoft 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.
strftime — SQLite
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)
extract(second_microsecond …) — MySQL, MariaDB
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)