MySQL: Group Timestamp Column by Month and Year

In blogs and forums, we can often see at the side of the website a list of links showing in what month and year how many posts have been written. 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 date_column" 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 allows us to return the year respectively the month of a TIMESTAMP, DATETIME or DATE column. With this, our GROUP BY could look like this:

GROUP BY YEAR(dat), MONTH(dat)

Thereby, we are grouping the column "dat" by year and by 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 would be the following:

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

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

Determine the Number of Records from a Table per Year

Of course, we can also structure our query in such 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

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.