How do I get the peak hours from my mysql database table "userlog"
My table is
id username calledtime
1 user1 01-02-2012 10:04:02
2 user2 01-02-2012 10:12:02
3 user1 01-02-2012 10:15:22
5 user4 01-02-2012 11:20:02
6 user6 01-02-2012 11:23:02
7 user8 01-02-2012 11:34:15
8 user7 01-02-2012 11:55:02
9 user8 01-02-2012 15:23:02
10 user6 01-02-2012 16:03:02
11 user9 01-02-2012 19:43:02
Here the peak time is 10 - 12. because most users are called in between in 10 and 12. How do I find out this ?
Can anyone help me out to get this
thanks
I think you just want a group by on the hour:
select extract(hour from calledtime) as hr,
count(*)
from t
group by extract(hour from calledtime)
order by 1
To get the peak for an hour, you would need to find the max for this. The easiest way is:
select extract(hour from calledtime) as hr,
count(*)
from t
group by extract(hour from calledtime)
order by count(*) desc
limit 1
You may also want date information as well as the hour; just include that in the select and group by.
链接地址: http://www.djcxy.com/p/5168.html上一篇: 在列中显示用户名