Wie man in PostgreSQL nach Wochen gruppiert

Müssen Sie Daten nach Woche in PostgreSQL analysieren? Hier erfahren Sie, wie Sie Ihre Datensätze mit Hilfe der eingebauten Datumsfunktionen gruppieren können, um klare, zeitbasierte Einblicke zu erhalten.

Die Gruppierung von Daten nach Wochen ist eine häufige Anforderung im Berichtswesen. Sie wird oft in Geschäftsberichten verwendet, um Kennzahlen wie wöchentliche Einnahmen, Benutzeranmeldungen oder Website-Traffic zu verfolgen. Die Gruppierung nach Wochen hilft, tägliche Schwankungen auszugleichen und gibt Ihnen einen besseren Überblick über Trends im Zeitverlauf. Dies ist besonders hilfreich, wenn Sie die Leistung über konsistente Zeitintervalle hinweg vergleichen möchten, wie z. B. bei einem Wachstum von Woche zu Woche oder einer wöchentlichen Kohortenanalyse.

PostgreSQL bietet leistungsstarke Werkzeuge für die Arbeit mit Daten, aber die Gruppierung nach Wochen - insbesondere wenn Sie einen bestimmten Starttag oder eine bestimmte Zeitzone benötigen - kann schwierig sein, wenn Sie nicht mit den richtigen Funktionen vertraut sind.

Dieser Artikel zeigt Ihnen, wie Sie in PostgreSQL mit Hilfe von date_trunc() und date_bin() nach Wochen gruppieren können, wie Sie Zeitzonen berücksichtigen und wie Sie einen benutzerdefinierten Wochenstarttag wie Mittwoch oder Donnerstag festlegen können.

Möchten Sie sich von Grund auf solide, praktische PostgreSQL-Kenntnisse aneignen? Versuchen Sie die SQL von A bis Z in PostgreSQL Kurs auf LearnSQL.de. Er führt Sie mit praktischen Übungen und Schritt-für-Schritt-Anleitungen vom Anfänger zum sicheren SQL-Anwender.

Gruppierung nach Woche mit date_trunc()

Die Funktion date_trunc() ist die gebräuchlichste Methode zur Gruppierung von Daten nach Woche in PostgreSQL. Sie schneidet einen Zeitstempel oder ein Datum auf den Beginn einer bestimmten Zeiteinheit wie Tag, Woche oder Monat ab.

Grundlegende Verwendung:

date_trunc('week', some_date)

Diese Funktion gibt ein neues Datum zurück, das den Beginn der ISO-Woche darstellt, d.h. Montag um 00:00:00 Uhr.

Beispiel:

Angenommen, Sie haben eine Spalte created_at und möchten die Anmeldungen pro Woche zählen:

SELECT
  date_trunc('week', created_at) AS week_start,
  COUNT(*) AS signups
FROM users
GROUP BY week_start
ORDER BY week_start;

Diese Abfrage gruppiert Benutzer nach der Woche, in der sie sich angemeldet haben, wobei jede Woche am Montag beginnt.

So könnte die Ausgabe dieser Abfrage aussehen:

Beachten Sie, dass das Ergebnis von date_trunc('week', some_date) sowohl das Datum als auch die Uhrzeit enthält, wobei die Uhrzeit auf 00:00:00 (Mitternacht) eingestellt ist.

Gruppierung nach Woche in einer bestimmten Zeitzone

Bei der Arbeit mit Zeitstempeln, insbesondere in globalen Anwendungen, ist die Berücksichtigung von Zeitzonen unerlässlich. Die Aktivität eines Benutzers könnte in einer Zeitzone am Sonntagabend stattfinden, in UTC aber als Montagmorgen erscheinen. Dies kann sich darauf auswirken, wie Daten in Wochen gruppiert werden, was zu falschen Berichten führt, wenn die Zeitzonen ignoriert werden.

Standardmäßig gruppiert PostgreSQL die Zeitstempel auf der Grundlage der Sitzungszeitzone, die normalerweise beim Verbindungsaufbau festgelegt wird. Das bedeutet, dass date_trunc('week', some_timestamptz_column) die aktuelle Zeitzoneneinstellung für die Sitzung verwendet. Wenn Sie die Zeitzone nicht explizit festlegen, verwendet PostgreSQL die Standardeinstellung aus Ihrer Server- oder Client-Konfiguration.

Um Konsistenz zu gewährleisten - insbesondere in Berichten - können Sie die AT TIME ZONE Klausel verwenden, um Zeitstempel in eine bestimmte Zeitzone zu konvertieren, bevor sie abgeschnitten werden.

Beispiel für die Verwendung:

SELECT
  date_trunc('week', created_at AT TIME ZONE 'UTC') AS week_start_utc,
  COUNT(*) AS signups
FROM users
GROUP BY week_start_utc
ORDER BY week_start_utc;

Diese Abfrage schneidet jeden created_at Zeitstempel auf den Wochenanfang in UTC ab, um sicherzustellen, dass die wöchentlichen Gruppierungen in allen Zeitzonen konsistent sind.

Wenn Sie mit einer timestamptz Spalte arbeiten und nach einer bestimmten Ortszeit gruppieren möchten, verwenden Sie:

date_trunc('week', created_at AT TIME ZONE 'America/New_York')

Dadurch werden Ihre Wochen auf den Wochenbeginn am Montag in New Yorker Ortszeit ausgerichtet.

Benutzerdefinierte Wochenanfangstage (z.B. Mittwoch oder Donnerstag)

Die Funktion date_trunc('week', some_date) in PostgreSQL richtet die Wochen immer so aus, dass sie am Montag beginnen, entsprechend der ISO-Wochendefinition. Dies ist festgelegt und kann nicht direkt über date_trunc() geändert werden.

Wenn Ihr Bericht einen Wochenbeginn an einem anderen Tag erfordert, z. B. Mittwoch oder Donnerstag, müssen Sie einen einfachen Workaround anwenden: Verschieben Sie das Datum vor der Trunkierung, und verschieben Sie es danach zurück.

Logik der Abhilfe

Damit die Woche an einem bestimmten Tag beginnt, subtrahieren Sie die entsprechende Anzahl von Tagen vor der Trunkierung und addieren sie anschließend wieder:

-- Week starting on Wednesday
date_trunc('week', some_date - interval '2 days') + interval '2 days'

Auf diese Weise wird der Kalender um 2 Tage nach hinten verschoben (aus Montag wird also Samstag), die Woche wird bis zum Beginn der angepassten Woche (Samstag) abgeschnitten und dann wieder bis Mittwoch nach vorne verschoben.

Beispiele für unterschiedliche Wochenanfangstage

Verwenden Sie dieses Muster innerhalb Ihrer SELECT, GROUP BY oder ORDER BY Klauseln, um die Daten an Ihre bevorzugte Wochenstruktur anzupassen.

Flexible Datums- und Zeitgruppierung mit date_bin() (PostgreSQL 14+)

Mit PostgreSQL 14 wurde eine leistungsfähige neue Funktion eingeführt: date_bin(). Sie gibt Ihnen mehr Kontrolle als date_trunc(), wenn Sie nach Zeit gruppieren, insbesondere wenn Sie Wochen an einem bestimmten Datum oder einem benutzerdefinierten Wochentag beginnen möchten.

date_bin() lässt Sie Zeitstempel in gleiche Zeitabschnitte wie Wochen, Tage oder Stunden gruppieren - ausgehend von einem beliebigen Datum und einer beliebigen Uhrzeit. Dieser Startpunkt wird als Anker bezeichnet.

Der Anker definiert, wann die erste Gruppe beginnt. PostgreSQL erstellt dann die anderen Gruppen auf dieser Grundlage - sowohl vorwärts als auch rückwärts in der Zeit. Wenn Ihr Anker also der 1. Januar 2025 ist und Sie nach 7 Tagen gruppieren, erstellt PostgreSQL wochenlange Intervalle, die mit diesem Datum beginnen, und auch frühere Intervalle, die dem gleichen Muster entsprechen.

Syntax

date_bin(interval, timestamp, anchor)

• interval: wie lang jede Gruppe sein soll (z.B. '7 days')
• timestamp: die Spalte, die Sie gruppieren wollen (z.B. created_at)
• anchor: das genaue Datum und die Uhrzeit, auf die sich die Gruppierung ausrichten soll

Beispiel: Gruppierung nach 1-Wochen-Intervallen beginnend am 1. Januar

SELECT
  date_bin('7 days', created_at, TIMESTAMP '2025-01-01') AS custom_week,
  COUNT(*) AS signups
FROM users
GROUP BY custom_week
ORDER BY custom_week;

Hier werden die Benutzer in 7-Tage-Intervalle gruppiert, die am 1. Januar 2025 beginnen, unabhängig davon, welcher Wochentag das ist. Sie können jedes beliebige Ankerdatum verwenden, z. B. den Beginn Ihres Geschäftsjahres oder den Tag der Markteinführung Ihres Produkts.

Verwenden Sie date_bin(), wenn Ihre Wochen an einem benutzerdefinierten Tag oder an einem exakten Datum beginnen sollen, wenn Sie Zeitspannen benötigen, die nicht auf Kalendereinheiten basieren (z. B. alle 10 Tage oder 6 Stunden), oder wenn Sie die volle Kontrolle darüber haben möchten, wie Ihre zeitbasierten Daten gruppiert werden.

Wenn Sie PostgreSQL 14 oder neuer verwenden, ist date_bin() eine flexible Alternative, mit der Sie Ihre wöchentlichen Berichte genauer gestalten und auf Ihre speziellen Bedürfnisse abstimmen können.

Schlussfolgerung

Die Gruppierung nach Wochen in PostgreSQL kann einfach oder hochgradig anpassbar sein, je nach Ihren Bedürfnissen. Sie können date_trunc() für Standard-ISO-Wochen (beginnend am Montag) verwenden oder kleine Anpassungen vornehmen, um Wochen an jedem beliebigen Tag zu beginnen. Für noch mehr Flexibilität - wie z.B. das Setzen eines bestimmten Ankerdatums oder die Verwendung von nicht standardisierten Zeitintervallen - istdate_bin() (verfügbar in PostgreSQL 14 und höher) das beste Werkzeug für diese Aufgabe.

Das Verständnis für die Arbeit mit Datumsangaben, Zeitzonen und Gruppierungslogik ist für jeden, der ernsthafte Datenanalysen oder Berichte in PostgreSQL erstellt, unerlässlich.

Wenn Sie bereit sind, Ihre PostgreSQL-Kenntnisse in der Praxis zu vertiefen, sollten Sie sich den Kurs SQL von A bis Z in PostgreSQL Track auf LearnSQL.de. Es ist ein praktischer, schrittweiser Lernpfad, der Ihnen helfen wird, von den Grundlagen zu fortgeschrittenen Techniken, wie sie in diesem Artikel behandelt werden, überzugehen.