MySQL group by day, month or year using a timestamp column

 

In Ruby on Rails, created_at and updated_at columns are MySQL timestamp columns.  GROUP BY is pretty useless on a timestamp column, unless you are trying to group rows that were added at the same second.  I needed to group by just the date, so in order to do this I had to manipulate the timestamp with a MySQL operator.  Using the DATE_FORMAT operator, you can easily group the timestamp column using any format you want.  For my 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 created_day

This query will give you something like this:

count(*) | created_day

126 | 2010-04-12

168 | 2010-04-13

169 | 2010-04-14

189 | 2010-04-15

187 | 2010-04-16

13 | 2010-04-17

Group by month:

select count(*), DATE_FORMAT(created_at,"%Y-%m") as created_month FROM widgets GROUP BY created_month

Group by year:

select count(*), DATE_FORMAT(created_at,"%Y") as created_year FROM widgets GROUP BY created_year

 

SELECT *
FROM(
SELECT *
FROM INVENTORY
ORDER BY vendors_no,
timestamp DESC
) as inv
GROUP BY vendors_no,
product_no

 

Source.

http://www.cowboycoded.com/2010/04/19/mysql-group-by-day-month-or-year-using-a-timestamp-column/

http://blog.chomperstomp.com/how-to-order-by-before-group-by-with-mysql/