5

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.

Ben
  • 60,438
  • 111
  • 314
  • 488

1 Answers1

5
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)
ʞɔıu
  • 47,148
  • 35
  • 106
  • 149
  • When I run the following query: SELECT * , HOUR( TIMESTAMP ) AS HOUR FROM ad_view LIMIT 0 , 30 It returns 838 as the hour, where it should be the number of hours in the current day. – Ben Oct 27 '09 at 20:04
  • Sorry, the number of hours into the current day (0-24) that the timestamp is. – Ben Oct 27 '09 at 20:05
  • are you sure that's really a timestamp? is it a timestamp integer stored in an int column or something? – ʞɔıu Oct 27 '09 at 20:28
  • hour() takes a time, not a unix timestamp. You want to use hour(from_unixtime(somedate)). Same for your other functions? – Keith Randall Oct 27 '09 at 21:58
  • if it's a unix timestamp int rather than a column of type timestamp, keith may be right – ʞɔıu Oct 28 '09 at 13:32
  • You had it right, I used this: [code]SELECT COUNT(id) as view_count, DAY(FROM_UNIXTIME(timestamp)) as day FROM ad_view WHERE timestamp > '.$start.' AND timestamp < '.$end.' GROUP BY day[/code] – Ben Oct 28 '09 at 13:36