I have a table where each row has a timestamp. Is there a query I can run that can count the number of rows per hour. Outputting a row for each hour with a field for the number of rows encompassed?
我有一个表,每行都有一个时间戳。我可以运行一个可以计算每小时行数的查询。使用包含行数的字段为每小时输出一行?
e.g
id timestamp
10 2010-09-19 21:05:05
11 2010-09-19 22:05:30
12 2010-09-19 23:05:05
13 2010-09-19 23:05:05
number of rows | hour
1 21
1 22
2 23
I guess there might be problems with using the hour as there will be duplicate hours... so maybe '2010-09-19 21' or just a number representing the period (e.g in the example 2010-09-19 21 is 1)
我猜使用小时可能有问题,因为会有重复的小时...所以也许'2010-09-19 21'或者只是一个代表期间的数字(例如在2010-09-19 21的例子中是1)
TIA
2 个解决方案
#1
17
It depends on how big your date range is. If all dates fall within a month, e.g., you could do this:
这取决于你的日期范围有多大。如果所有日期都在一个月内,例如,你可以这样做:
select day(timestamp) as Day, hour(timestamp) as Hour, count(*) as Count
from MyTable
where timestamp between :date1 and :date2
group by day(timestamp), hour(timestamp)
You can group by year and month as well if you need to further separate your data.
如果您需要进一步分离数据,也可以按年和月分组。
#2
2
SELECT HOUR(hour) as hour, COUNT(*) as num_rows FROM `TABLE` GROUP BY HOUR(hour)
#1
17
It depends on how big your date range is. If all dates fall within a month, e.g., you could do this:
这取决于你的日期范围有多大。如果所有日期都在一个月内,例如,你可以这样做:
select day(timestamp) as Day, hour(timestamp) as Hour, count(*) as Count
from MyTable
where timestamp between :date1 and :date2
group by day(timestamp), hour(timestamp)
You can group by year and month as well if you need to further separate your data.
如果您需要进一步分离数据,也可以按年和月分组。
#2
2
SELECT HOUR(hour) as hour, COUNT(*) as num_rows FROM `TABLE` GROUP BY HOUR(hour)