ansaurus

Question

Select and group rows in summary by hour

Answer 1

+1 A:

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 2009-10-27 19:59:25

When I run the following query:SELECT * , HOUR( TIMESTAMP ) AS HOUR FROM ad_viewLIMIT 0 , 30It returns 838 as the hour, where it should be the number of hours in the current day.

Webnet 2009-10-27 20:04:42

Sorry, the number of hours into the current day (0-24) that the timestamp is.

Webnet 2009-10-27 20:05:39

are you sure that's really a timestamp? is it a timestamp integer stored in an int column or something?

ʞɔıu 2009-10-27 20:28:53

hour() takes a time, not a unix timestamp. You want to use hour(from_unixtime(somedate)). Same for your other functions?

Keith Randall 2009-10-27 21:58:17

if it's a unix timestamp int rather than a column of type timestamp, keith may be right

ʞɔıu 2009-10-28 13:32:54

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]

Webnet 2009-10-28 13:36:23

ansaurus

tags:

views:

answers:

Select and group rows in summary by hour

related questions