以完整的日期时间值作为结果计算 SQL Server 中每小时的行数 [英] Count rows per hour in SQL Server with full date-time value as result

问题描述

如何计算 SQL Server 中每小时的行数，结果是完整的日期时间.

How can I count the number of rows per hour in SQL Server with full date-time as result.

我已经试过了，但它只返回小时

I've already tried this, but it returns only the hours

SELECT DATEPART(HOUR,TimeStamp), Count(*)
FROM [TEST].[dbo].[data]
GROUP BY DATEPART(HOUR,TimeStamp)
ORDER BY DATEPART(HOUR,TimeStamp)

现在的结果是:

Hour   Occurrence 
----   ----------
10     2157
11     60740
12     66189
13     77096
14     90039

但我需要这个:

Timestamp               Occurrence
-------------------     ----------
2013-12-21 10:00:00     2157
2013-12-21 11:00:00     60740
2013-12-21 12:00:00     66189
2013-12-21 13:00:00     77096
2013-12-21 14:00:00     90039
2013-12-22 09:00:00     84838
2013-12-22 10:00:00     64238

推荐答案

您实际上需要将 TimeStamp 舍入到小时.在 SQL Server 中，这有点难看，但很容易做到:

You actually need to round the TimeStamp to the hour. In SQL Server, this is a bit ugly, but easy to do:

SELECT dateadd(hour, datediff(hour, 0, TimeStamp), 0) as TimeStampHour, Count(*)
FROM [TEST].[dbo].[data]
GROUP BY dateadd(hour, datediff(hour, 0, TimeStamp), 0)
ORDER BY dateadd(hour, datediff(hour, 0, TimeStamp), 0);

这篇关于以完整的日期时间值作为结果计算 SQL Server 中每小时的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！