33 Votes

MySQL: Group Timestamp Column by Month and Year

Tip by Computer Expert | 06.08.2012 at 09:44

In blogs and forums, we often see on the side of a page 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 but the number of posts per day or specific date - and we do not want to have this like that.

The key lies in the use of the MySQL functions YEAR() and MONTH(), which allows us to return the year and month of a TIMESTAMP column. With this, our GROUP BY looks like this:

GROUP BY YEAR(dat), MONTH(dat)

Thereby, we are grouping the column "dat" by year and by month. An example for a complete SELECT would look like this: 

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

Accordingly, we count the ID or 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:

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

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.

ReplyPositiveNegative

About the Author

AvatarThe author has not added a profile short description yet.
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.

Participate

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