Using the DATE_FORMAT operator, you can easily group the timestamp, date or datetime column using any format you want.
For example, I needed to group rows that were added on the same day. Here is my query:
select count(*), DATE_FORMAT(created_at,"%Y-%m-%d") as created_day FROM widgets GROUP BY DATE_FORMAT(created_at,"%Y-%m-%d")
count(*) | created_day126 | 2012-04-12168 | 2012-04-13169 | 2012-04-14189 | 2012-04-15187 | 2012-04-16131 | 2012-04-17
Similarly Group by month:
select count(*), DATE_FORMAT(created_at,"%Y-%m") as created_month FROM widgets GROUP BY DATE_FORMAT(created_at,"%Y-%m")
Similarly Group by year:
select count(*), DATE_FORMAT(created_at,"%Y") as created_year FROM widgets GROUP BY DATE_FORMAT(created_at,"%Y")
Note: Only a member of this blog may post a comment.