Tutorial: Datums- und Zeitfunktionen in SQL (PostgreSQL)
PostgreSQL bietet eine breite Palette an Datums- und Zeitfunktionen, die für die Verarbeitung, Berechnung und Formatierung von Datums- und Zeitwerten nützlich sind. In diesem Tutorial lernst Du die Grundlagen des Arbeitens mit Datums- und Zeitfunktionen in PostgreSQL kennen und wie Du diese Funktionen für verschiedene Analysen und Abfragen nutzen kannst.
Datentypen für Datum und Zeit in PostgreSQL
PostgreSQL bietet mehrere Datentypen für Datum und Zeit:
DATE: Enthält nur das Datum (z. B.2023-01-01).TIME [WITHOUT TIME ZONE]: Enthält nur die Zeit (z. B.14:30:00).TIMESTAMP [WITHOUT TIME ZONE]: Enthält Datum und Zeit (z. B.2023-01-01 14:30:00).TIMESTAMPTZoderTIMESTAMP WITH TIME ZONE: Enthält Datum und Zeit inklusive Zeitzone (z. B.2023-01-01 14:30:00+00).
Aktuelles Datum und aktuelle Zeit abrufen
PostgreSQL bietet Funktionen zum Abrufen des aktuellen Datums und der aktuellen Zeit.
SELECT CURRENT_DATE; -- Gibt das heutige Datum zurück
SELECT CURRENT_TIME; -- Gibt die aktuelle Uhrzeit zurück
SELECT CURRENT_TIMESTAMP; -- Gibt das aktuelle Datum und die Uhrzeit zurück
SELECT NOW(); -- Entspricht CURRENT_TIMESTAMPDiese Funktionen sind hilfreich, um die aktuellen Zeitangaben in Deine Abfragen einzubeziehen.
Extrahieren von Datumsteilen
Mit der EXTRACT-Funktion kannst Du bestimmte Teile eines Datums- oder Zeitwertes abrufen, z. B. Jahr, Monat, Tag, Stunde oder Minute.
SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-01-15 14:30:00'); -- Gibt das Jahr zurück: 2023
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-01-15 14:30:00'); -- Gibt den Monat zurück: 1
SELECT EXTRACT(DAY FROM TIMESTAMP '2023-01-15 14:30:00'); -- Gibt den Tag zurück: 15
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-01-15 14:30:00'); -- Gibt die Stunde zurück: 14
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-01-15 14:30:00'); -- Gibt die Minute zurück: 30
SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-01-15 14:30:00'); -- Gibt die Sekunde zurück: 0EXTRACT ist besonders nützlich, wenn Du zeitbasierte Berechnungen oder Filterungen auf Grundlage spezifischer Datumsteile durchführen möchtest.
Datumsarithmetik und -berechnungen
PostgreSQL ermöglicht Berechnungen mit Datum und Zeit, wie das Hinzufügen oder Subtrahieren von Tagen, Monaten oder Jahren.
Tage addieren und subtrahieren
SELECT DATE '2023-01-01' + INTERVAL '10 days'; -- Ergebnis: 2023-01-11
SELECT DATE '2023-01-15' - INTERVAL '5 days'; -- Ergebnis: 2023-01-10Mit diesen einfachen Berechnungen kannst Du Zeiträume festlegen und analysieren, wie sich bestimmte Datumswerte verändern.
Jahre und Monate addieren und subtrahieren
SELECT DATE '2023-01-01' + INTERVAL '1 year'; -- Ergebnis: 2024-01-01
SELECT DATE '2023-01-15' + INTERVAL '2 months'; -- Ergebnis: 2023-03-15
SELECT DATE '2023-01-15' - INTERVAL '6 months'; -- Ergebnis: 2022-07-15Du kannst das INTERVAL anpassen, um Jahre, Monate, Wochen, Tage oder Stunden zu ändern und Zeitintervalle flexibel anzupassen.
Differenzen zwischen Datumswerten berechnen
Mit PostgreSQL kannst Du die Differenz zwischen zwei Datums- oder Zeitwerten berechnen und erhältst das Ergebnis in Form eines Intervalls.
SELECT DATE '2023-01-15' - DATE '2023-01-01' AS differenz; -- Ergebnis: 14 Tage
SELECT AGE(TIMESTAMP '2023-01-15', TIMESTAMP '2022-01-01'); -- Ergebnis: 1 Jahr 14 TageDie AGE-Funktion berechnet die Zeitdifferenz und gibt das Ergebnis in Jahren, Monaten und Tagen zurück. Sie eignet sich besonders gut, um die Zeitspanne zwischen zwei Ereignissen zu analysieren.
Datumsfunktionen für zeitliche Gruppierung
PostgreSQL bietet Funktionen, um Datumswerte nach bestimmten Intervallen zu gruppieren, z. B. nach Jahr, Monat oder Woche. Diese Techniken sind besonders hilfreich bei der Erstellung zeitbasierter Analysen.
Gruppieren nach Jahr oder Monat
SELECT EXTRACT(YEAR FROM datum) AS jahr, COUNT(*)
FROM daten_tabelle
GROUP BY jahr;SELECT EXTRACT(MONTH FROM datum) AS monat, COUNT(*)
FROM daten_tabelle
GROUP BY monat;Durch Gruppierung nach Jahr oder Monat kannst Du beispielsweise Verkaufszahlen oder Benutzeraktivität nach Jahr oder Monat analysieren.
Gruppieren nach Woche
SELECT DATE_TRUNC('week', datum) AS woche, COUNT(*)
FROM daten_tabelle
GROUP BY woche;Die Funktion DATE_TRUNC kürzt das Datum auf die erste Tag der jeweiligen Woche, wodurch Du die Daten wöchentlich gruppieren kannst.
Formatieren von Datums- und Zeitwerten
Die Funktion TO_CHAR ermöglicht es, Datums- und Zeitwerte in bestimmten Formaten auszugeben, was besonders nützlich ist, wenn Du Ergebnisse in einer bestimmten Darstellungsform benötigst.
SELECT TO_CHAR(DATE '2023-01-15', 'DD.MM.YYYY'); -- Ergebnis: 15.01.2023
SELECT TO_CHAR(TIMESTAMP '2023-01-15 14:30:00', 'HH24:MI:SS'); -- Ergebnis: 14:30:00
SELECT TO_CHAR(TIMESTAMP '2023-01-15 14:30:00', 'YYYY-MM-DD HH:MI AM'); -- Ergebnis: 2023-01-15 02:30 PMDie Funktion TO_CHAR ist flexibel und ermöglicht viele Formate. DD, MM, YYYY und HH24 sind gängige Formatangaben, die Du kombinieren kannst.
Arbeiten mit Zeitstempeln und Zeitzonen
PostgreSQL unterstützt die Arbeit mit verschiedenen Zeitzonen. Wenn Du mit TIMESTAMP WITH TIME ZONE arbeitest, kannst Du Zeitzonen explizit angeben und konvertieren.
Zeitzonen zuweisen
SELECT TIMESTAMP '2023-01-15 14:30:00' AT TIME ZONE 'UTC'; -- Ergebnis: UTC-Zeit
SELECT TIMESTAMP '2023-01-15 14:30:00' AT TIME ZONE 'America/New_York'; -- Ergebnis: New York-ZeitZeitzonen konvertieren
Du kannst zwischen Zeitzonen konvertieren, indem Du den Wert einem neuen Zeitzonenbereich zuweist.
SELECT TIMESTAMPTZ '2023-01-15 14:30:00+00' AT TIME ZONE 'America/New_York';Dieser Befehl konvertiert den angegebenen Zeitstempel von UTC in die Zeitzone America/New_York.
Anwendungsbeispiel: Umsatzanalyse nach Quartal
In diesem Beispiel berechnen wir den Umsatz einer hypothetischen Tabelle umsatz nach Quartal.
SELECT
DATE_TRUNC('quarter', datum) AS quartal,
SUM(betrag) AS gesamt_umsatz
FROM
umsatz
GROUP BY
quartal
ORDER BY
quartal;Mit DATE_TRUNC('quarter', datum) kürzen wir das Datum auf das Quartal. Anschließend summieren wir den Umsatz pro Quartal.
