如何按日期时间列对自定义小时进行分组

How can I group by date time column for custom hour, for example every 5 Hour or every 12 Hour or every 24 hour.

如何按日期时间列分组自定义小时,例如每5小时或每12小时或每24小时。

Select CreateOn, Count(*) From Table1
Group By ?????

2 个解决方案

#1

You should select start date/time, '2011-01-01' in this example and then use DATEDIFF() function to get time difference in hours between start date and CreateOn. Then use / operator to get an integer interval number (5 hours in this example).

您应该在此示例中选择开始日期/时间'2011-01-01',然后使用DATEDIFF()函数获取开始日期和CreateOn之间的小时差。然后使用/运算符获取整数区间数(在此示例中为5小时)。

Select 
min(DATEADD(HOUR,DATEDIFF (HOUR,'2011-01-01',CreateOn )/5*5,'2011-01-01')) 
   as Start_time,
max(DATEADD(HOUR,(DATEDIFF (HOUR,'2011-01-01',CreateOn )/5+1)*5,'2011-01-01')) 
   as End_Time,
DATEDIFF (HOUR,'2011-01-01',CreateOn )/5 as Interval_Number, 
Count(*) as _Count
From Table1
Group By DATEDIFF (HOUR,'2011-01-01',CreateOn )/5

#2

If I understand your question correctly, you could try something like this:

如果我理解你的问题,你可以尝试这样的事情:

SELECT CreateOn, Count(*) FROM Table1 GROUP BY (DATEPART(MINUTE, [Date]) % 12)