Runden und Abschneiden von Zeitdaten: DATE_TRUNC()

DATE_TRUNC() ist ein mächtiges Tool, mit dem du Zeitwerte auf eine bestimmte Zeiteinheit abschneiden kannst. Zum Beispiel kannst du damit einen Timestamp auf den Tages-, Monats-, Jahres- oder Stundenanfang runden. Das ist besonders praktisch, wenn du Daten nach Zeiträumen analysieren willst (zum Beispiel, wenn du Bestellungen nach Tag, Monat oder Jahr gruppieren möchtest).

Stell dir ein Datum und eine Uhrzeit wie einen langen Textstring vor, in dem du Stunden, Minuten, Sekunden hast. Die Funktion DATE_TRUNC() nimmt diesen String und "schneidet" das Überflüssige ab, sodass nur der Teil übrig bleibt, den du brauchst. Zum Beispiel:

• Du willst das Datum 2023-10-01 15:30:45 auf den Tagesanfang abschneiden. Das Ergebnis ist dann 2023-10-01 00:00:00.
• Oder du willst nur die erste Sekunde der Stunde behalten, also 2023-10-01 15:00:00.

Syntax

Die Syntax der Funktion DATE_TRUNC() sieht so aus:

DATE_TRUNC(field, source)

• field — das ist die Zeiteinheit, auf die du das Datum "kappen" willst. Zum Beispiel year, month, day, hour, minute.
• source — das ist der Zeitwert, den du abschneiden willst. Das kann eine Spalte vom Typ TIMESTAMP sein oder das Ergebnis eines anderen Funktionsaufrufs, zum Beispiel NOW().

Ein einfaches Beispiel:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- Ergebnis: 2023-10-01 00:00:00

Unterstützte Felder

Hier ist eine Liste einiger unterstützter Zeiteinheiten, die du in DATE_TRUNC() verwenden kannst:

Je kleiner die Zeiteinheit, desto genauer ist das Abschneiden. Übrigens, die Woche startet am Sonntag :)

Beispiele für die Nutzung von DATE_TRUNC()

Abschneiden auf den Tagesanfang. In diesem Beispiel nehmen wir einen Timestamp und runden ihn auf den Tagesanfang:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- Ergebnis: 2023-10-01 00:00:00

Abschneiden auf den Monatsanfang. Jetzt schneiden wir das Datum auf den Monatsanfang ab:

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- Ergebnis: 2023-10-01 00:00:00

Abschneiden auf den Jahresanfang. Probieren wir, das Datum auf den Jahresanfang zu runden:

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- Ergebnis: 2023-01-01 00:00:00

Verwendung mit aktueller Zeit (NOW()). Wenn du immer mit dem aktuellen Datum und der aktuellen Uhrzeit arbeiten willst, kannst du DATE_TRUNC() und NOW() kombinieren:

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- Das Ergebnis hängt von der aktuellen Zeit ab, zum Beispiel: 2023-10-01 15:00:00

Bestellungen nach Monaten gruppieren. Jetzt ein etwas praktischeres Beispiel. Angenommen, wir haben eine Tabelle mit Bestellungen, in der für jeden Eintrag das Bestelldatum steht. Wir wollen die Anzahl der Bestellungen pro Monat zählen:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Ergebnis:

Praktische Anwendungsfälle

Analyse von Zeitdaten nach Perioden: Willst du wissen, wie viele User sich jedes Jahr, jeden Monat oder jeden Tag registriert haben? Nutze DATE_TRUNC() zum Gruppieren der Daten.

Berichte erstellen: Das richtige Runden von Zeitstempeln macht deine Reports viel lesbarer.

Vergleich von Datum und Uhrzeit: Wenn du Zeitdaten mit hoher Genauigkeit hast (zum Beispiel mit Millisekunden), schneide sie auf das gewünschte Level ab, damit der Vergleich korrekt ist.

Typische Fehler bei der Verwendung von DATE_TRUNC()

Verwendung von nicht unterstützten Feldern. Zum Beispiel wird das Feld millisecond nicht unterstützt, und wenn du es benutzt, bekommst du einen Fehler.

Falscher Datentyp. Die Funktion DATE_TRUNC() funktioniert nur mit Zeitdatentypen wie TIMESTAMP. Wenn du ihr einen String gibst, bekommst du einen Fehler.

Rundungsfehler. Denk dran, DATE_TRUNC() schneidet die Zeit immer auf den Anfang der angegebenen Zeiteinheit ab. Wenn du wirklich runden willst, solltest du andere Methoden verwenden.