MySQL: Timestamp Spalte nach Monat und Jahr gruppieren

In Blogs oder Foren sehen wir oft an einer Seite der Webseite eine Liste mit Links, die anzeigen, in welchem Monat und in welchem Jahr wie viele Beiträge geschrieben wurden. Wenn man auf einen dieser Links klickt bekommt man die entsprechenden Artikel und Beiträge aus genau diesem Monat angezeigt.

Wie aber lässt sich die Anzahl von Beiträgen je Monat bestimmen? Schließlich haben wir die Beiträge in unserer MySQL-Tabelle zeitlich mit der Hilfe einer TIMESTAMP- oder DATE-Spalte eingeordnet und ein "GROUP BY spalte_date" würde uns nicht die Anzahl der Artikel pro Monat oder Jahr ausgeben sondern die Anzahl der Artikel je Tag beziehungsweise je einem spezifischem Datum oder sogar je einer spezifischen Zeit - und ein Ergebnis dieser Art wollen wir natürlich nicht.

Der Schlüssel liegt in dem Benutzen der MySQL-Funktionen YEAR() und MONTH(), die uns das Jahr beziehungsweise den Monat einer TIMESTAMP-, DATETIME oder DATE-Spalte zurückgeben können, indem sie diesen Bestandteil aus dem Datum extrahieren.

Unser GROUP BY könnte also so aussehen:

GROUP BY YEAR(dat), MONTH(dat)

Damit gruppieren wir nach dem Jahr und nach dem Monat der Spalte "dat".

Anzahl der Einträge aus einer Tabelle pro Monat bestimmen

Schauen wir uns dazu einen konkreten Anwendungsfall an. Ein Beispiel für eine komplette SELECT-Anfrage haben wir hier:

SELECT COUNT(id), RPAD('0', 2, MONTH(dat)), YEAR(dat) FROM tabelle 
GROUP BY YEAR(dat), MONTH(dat);

Mit diesem SELECT zählen wir die ID (oder wahlweise natürlich auch jede andere eindeutige Zuordnung der Datensätze) und lesen auch gleich Monat und Jahr der gezählten Beiträge aus. Das RPAD sorgt dafür, dass wir unseren Monat gleich in zwei Zeichen mit führenden Nullern erhalten und nicht erst anschließend formatieren müssen.

Ein mögliches Ergebnis dieser Abfrage wäre das folgende:

ANZAHL   MONAT   JAHR
12       01      2024
37       02      2024
83       03      2024
78       04      2024
95       05      2024

Mit PHP lässt sich dieses Ergebnis dann einfach wie gewünscht formatieren, so dass wir eine Darstellung wie zum Beispiel "01/2024 (12)" oder wie wir es ansonsten haben möchte bekommen und das ganze mit der Suchfunktion nach den Artikeln verlinken können.

Anzahl der Einträge aus einer Tabelle pro Jahr bestimmen

Natürlich können wir unsere Abfrage auch so gestalten, dass wir statt den Einträgen pro Monat die Einträge pro Jahr ausgeben. Wir müssen dafür nur den Teil mit dem Monat weglassen:

SELECT COUNT(id), YEAR(dat) FROM tabelle
GROUP BY YEAR(dat);

Ein mögliches Ergebnis könnte dann so aussehen:

ANZAHL   JAHR
67       2021
39       2022
73       2023
29       2024

Dies bietet sich zum Beispiel für den Fall an, dass wir eine Webseite mit nur sehr wenigen Artikeln haben und eine Monatsauflösung daher zu klein wäre.