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.

Bedeutungextract Feldname
JahrYEAR
MonatMONTH
Tag des MonatesDAY
Stunde (0-23)HOUR
MinuteMINUTE
Sekunden (inklusive Bruchteil)SECOND
Zeitzone: StundeTIMEZONE_HOUR
Zeitzone: MinuteTIMEZONE_MINUTE

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.

BigQueryaDb2 (LUW)bdMariaDBcdMySQLcdOracle DBePostgreSQLSQL ServerdSQLitedfgextract(… from <datetime>)extract(… from <interval>)cast(<timestamp> as date)cast(<timestamp> as time)
  1. Keine Zeitzonen • SECOND ohne Buchteile
  2. Keine Zeitzonen
  3. Keine Zeitzonen • SECOND ohne Buchteile. Verwende SECOND_MICROSECOND
  4. Unterstützt den Datentyp interval nicht
  5. Siehe „Vorsicht: Oracle-Datenbank“ oben
  6. Verwende stattdessen date(<timestamp>)
  7. Verwende stattdessen time(<timestamp>)

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.

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

BigQueryacfgDb2 (LUW)adhiMariaDBegMySQLegOracle DBPostgreSQLbfSQL ServerSQLitequarterdoydayofyeardowdayofweekweekisoweekmicrosecondepoch
  1. Sonntag = 1
  2. Sonntag = 0
  3. Wochen beginnen Sonntags, die erste Woche ist jene mit dem ersten Sonntag im Jahr • Verwende extract(isoyear) um das zugehörige Jahr zu erhalten
  4. ISO 8601 Wochendatum (erster Donnerstag im Jahr)
  5. Wochen beginnen Sonntags, die erste Woche ist jene mit dem ersten Sonntag im Jahr
  6. ISO 8601 Wochendatum (erster Donnerstag im Jahr) • Verwende extract(isoyear) um das zugehörige Jahr zu erhalten
  7. Inklusive sekunden: 12.3456789 sekunden ergibt 123456789
  8. Anders als in der Dokumentation dargestellt, enhält es keine Sekunden
  9. Inklusive Bruchteilen

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.

BigQueryDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitedatepartstrftimeextract(second_microsecond …)

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)

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.

Ü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»

Fußnoten

  1. SQL:2016, Part 2, §6.30, Syntax Rule 7

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

  3. Der Oracle-Type date unterstützt jedoch weder Sekundenbruchteile, noch Zeitzonen.

  4. Durch die Zeitzonen verschiebt sich die Schaltsekunde natürlich. Allgemein gesagt endet also nicht jede Minute mit der 59. Sekunde.

  5. Die Zeit, in der Douglas Noel Adams 42 Jahre alt war.

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

  7. „Proprietäre“ bezieht sich hierbei auf die Sicht des SQL-Standards. strftime ist dennoch standardisiert – nicht von SQL, aber von POSIX.

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„modern SQL“ 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