SQL extract — Zugriff auf Datums- und Zeit-Felder

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.

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.

trunc(<timestamp>)

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

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 mittle extract, to_char oder Ähnlichem umzusetzen:4

WHERE 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 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!

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)