55 Votes

MySQL: Group Timestamp Column by Month and Year

Tip by Stefan Trost | Last update on 2023-01-07 | Created on 2012-08-06

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:


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 

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:

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:


A possible result could then look like this:

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.


About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile


Related Topics

PHP: Current Date and Time

Tutorial | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.