MySQL: Group Timestamp Column by Month and Year

In blogs and forums, we can often see a list of links showing in what month and year how many posts have been written at the side of the website. If you click on one of these links, you get the relevant articles and posts for just this month.

But how to determine the number of articles per month? Finally, we have the articles in our MySQL table chronologically ordered with a TIMESTAMP or DATE column and a "GROUP BY column_date" would not return the number of articles per month or year but the number of posts per specific day or even per specific time - and we do not want to have a result like that.

The key lies in the use of the MySQL functions YEAR() and MONTH(), which allow us to return the year respectively the month of a TIMESTAMP, DATETIME or DATE column, by extracting this part of the date.

With this, our GROUP BY could look like this:

GROUP BY YEAR(dat), MONTH(dat)

Thereby, we are grouping the column "dat" by the year and the month.

Determine the Number of Records from a Table per Month

Let's look at a specific use case. Here is an example for a complete SELECT query:

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

Accordingly, we count the ID (or optionally any other unique assignment of our data) and in the same step, we are reading out the month and the year of the appropriate contributions. The RPAD ensures that our month is read out with leading zeros, so that we do not have to format the number afterwards.

One possible result of this query could look like the following:

COUNT    MONTH   YEAR
12       01      2024
37       02      2024
83       03      2024
78       04      2024
95       05      2024

With PHP, these numbers can simply be formatted, so that we can get a representation like "01/2024 (12)", for example, or as you might like. Of course, you can also add a link on each row, so that the corresponding articles can be displayed.

Determine the Number of Records from a Table per Year

Of course, we can also structure our query in a way that we count the items per year instead of per month. We only have to leave out the part with the month to get that:

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

A possible result could then look like this:

COUNT   YEAR
67      2021
39      2022
73      2023
29      2024

This could be a good idea, for example, in the case that we have a website with very few articles so that a monthly resolution would therefore be too small.