Sunday, May 6, 2012

Count Record in every hour of a given day (MySQL)

Here is a query to count records gathered in every hour of a given day. The date_visited is a 24 hour time format which is of timestamp data type. The idea came from a fellow developer..
1:  select  
2:       hour(time(date_visited)) as hour_of_day_24_hour_format,  
3:       count(hour(time(date_visited))) as count_per_hour from tblpropsale_v2  
4:    where date(date_visited) = '2012-05-07' and web_id = 642 and is_visited = 1  
5:       group by hour(time(date_visited)) order by hour(time(date_visited)) asc;  


Post a Comment