Select and group rows in a clock summary

Question

I have a table containing view / click records. The time is stored in the unix timestamp, and I need to pull all of them out for a specific month / day (based on the timestamps), but more importantly, and the part that I don’t know how to do is to group them by the clock. I need to be able to do this in one request, and not every hour.

The database is MySQL, the language is PHP.

Answer 1

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)