Select and group rows in summary by hour
I have a table containing view/click records. The time is stored in a unix timestamp and I need to be able to pull out all of them within the specific month/day (based off of timestamps), but more importantly and the part I don't know how to do is group them by hour. I need to be able to do this in a single query rather than looping through each hour.
Database is MySQL, language is PHP.
select hour(somedate), count(*) from yourtable group by hour(somedate)
If you need all three:
select month(somedate), day(somedate), hour(somedate), count(*) from yourtable group by month(somedate), day(somedate), hour(somedate)