mysql每小时行数 [英] mysql number of rows per hour

问题描述

我有一个表，其中每行都有一个时间戳.我是否可以运行一个查询，该查询可以计算每小时的行数.每小时输出一行，其中包含包含行数的字段?

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?

例如

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

我想使用小时可能会出现问题，因为会有重复的小时...所以可能是'2010-09-19 21'或仅是代表期间的数字(例如，在示例2010-09-19 21中是1)

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)

TIA

推荐答案

这取决于您的日期范围.如果所有日期都在一个月之内，例如，您可以这样做:

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.

这篇关于mysql每小时行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！