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.
About the Author
The author has not added a profile short description yet.
Question | 3 Answers
Tutorial | 0 Comments
Question | 2 Answers
Question | 1 Answer
Question | 1 Answer
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.