如何将时间按小时或10分钟分组

like when I do

像我一样

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date]

how can I specify the group period ?

如何指定组周期?

MS SQL 2008

2nd Edit

第二次编辑

I'm trying

我试着

SELECT MIN([Date]) AS RecT, AVG(Value)
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY (DATEPART(MINUTE, [Date]) / 10)
  ORDER BY RecT

changed %10 to / 10. is it possible to make Date output without milliseconds ?

改变%10到/ 10。是否可以在没有毫秒的情况下输出日期?

9 个解决方案

#1

169

finally done with

终于完成了

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

#2

I'm super late to the party, but this doesn't appear in any of the existing answers:

我在派对上迟到了，但在现有的答案中没有出现:

group by dateadd(minute, datediff(minute, 0, DT.[Date]) / 10 * 10, 0)

The 10 and minute terms can be changed to any number and datepart, respectively.
10和分钟的术语可以分别更改为任意数字和日期部分。
It is a datetime value, which means:
- It works fine across long time intervals (no collision between years or anything).
- 它可以在长时间间隔内正常工作(不会在年之间发生碰撞)。
- Including it in the select statement will give your output a column with pretty output truncated at the level you specify.
- 在select语句中包含它将为您的输出提供一个在指定级别上截断的输出列。
它是一个datetime值，意思是:它可以在很长的时间间隔内正常工作(不会在年份之间发生冲突)。在select语句中包含它将为您的输出提供一个在指定级别上截断的输出列。

SELECT dateadd(minute, datediff(minute, 0, AA.[Date]) / 10 * 10, 0) as [Date_Truncated],
    count(*) as [Records_in_Interval],
    avg(Value) as [Average_Value]
FROM [FRIIB].[dbo].[ArchiveAnalog] as AA
GROUP BY dateadd(minute, datediff(minute, 0, AA.[Date]) / 10 * 10, 0)
ORDER BY [Date_Truncated]

#3

In T-SQL you can:

在t - sql,您可以:

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date], DATEPART(hh, [Date])

或

by minute use DATEPART(mi, [Date])

按分钟使用日期部分(mi，[日期])

或

by 10 minutes use DATEPART(mi, [Date]) / 10 (like Timothy suggested)

使用日期部分(mi， [Date]) / 10分钟(如Timothy建议的)

#4

For a 10 minute interval, you would

间隔10分钟，你就会

GROUP BY (DATEPART(MINUTE, [Date]) / 10)

As was already mentioned by tzup and Pieter888... to do an hour interval, just

tzup和Pieter888已经提到过…间隔一小时

GROUP BY DATEPART(HOUR, [Date])

#5

The original answer the author gave works pretty well. Just to extend this idea, you can do something like

作者给出的最初答案相当不错。为了扩展这个概念，你可以做一些类似的事情

group by datediff(minute, 0, [Date])/10

which will allow you to group by a longer period then 60 minutes, say 720, which is half a day etc.

这样你就可以长时间的分组然后60分钟，比如720分钟，也就是半天。

#6

Should be something like

应该是类似的

select timeslot, count(*)  
from 
    (
    select datepart('hh', date) timeslot
    FROM [FRIIB].[dbo].[ArchiveAnalog]  
    ) 
group by timeslot

(Not 100% sure about the syntax - I'm more an Oracle kind of guy)

(对语法不是100%确定——我更像是Oracle之类的人)

In Oracle:

在Oracle中:

SELECT timeslot, COUNT(*) 
FROM
(  
    SELECT to_char(l_time, 'YYYY-MM-DD hh24') timeslot 
    FROM
    (
        SELECT l_time FROM mytab  
    )  
) GROUP BY timeslot

#7

For MySql:

MySql:

GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);

#8

My solution is to use a function to create a table with the date intervals and then join this table to the data I want to group using the date interval in the table. The date interval can then be easily selected when presenting the data.

我的解决方案是使用一个函数创建一个具有日期间隔的表，然后使用表中的日期间隔将这个表连接到我想要分组的数据。在显示数据时，可以很容易地选择日期间隔。

CREATE FUNCTION [dbo].[fn_MinuteIntervals]
    (
      @startDate SMALLDATETIME ,
      @endDate SMALLDATETIME ,
      @interval INT = 1
    )
RETURNS @returnDates TABLE
    (
      [date] SMALLDATETIME PRIMARY KEY NOT NULL
    )
AS
    BEGIN
        DECLARE @counter SMALLDATETIME
        SET @counter = @startDate
        WHILE @counter <= @endDate
            BEGIN
                INSERT INTO @returnDates VALUES ( @counter )
                SET @counter = DATEADD(n, @interval, @counter)
            END
        RETURN
    END

#9

For SQL Server 2012, though I believe it would work in SQL Server 2008R2, I use the following approach to get time slicing down to the millisecond:

对于SQL Server 2012，虽然我相信它可以在SQL Server 2008R2中工作，但是我使用以下方法将时间分割到毫秒级:

DATEADD(MILLISECOND, -DATEDIFF(MILLISECOND, CAST(time AS DATE), time) % @msPerSlice, time)

This works by:

这是通过:

Getting the number of milliseconds between a fixed point and target time:
@ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)
获取定点和目标时间之间的毫秒数:@ms = DATEDIFF(毫秒，强制转换(时间作为日期)，时间)
Taking the remainder of dividing those milliseconds into time slices:
@rms = @ms % @msPerSlice
将剩下的毫秒划分为时间片:@rms = @ms % @msPerSlice
Adding the negative of that remainder to the target time to get the slice time:
DATEADD(MILLISECOND, -@rms, time)
将余数的负值添加到目标时间以获得切片时间:DATEADD(毫秒，-@rms，时间)

Unfortunately, as is this overflows with microseconds and smaller units, so larger, finer data sets would need to use a less convenient fixed point.

不幸的是，正如微秒和更小的单位所造成的溢出一样，更大、更精细的数据集需要使用不太方便的定点。

I have not rigorously benchmarked this and I am not in big data, so your mileage may vary, but performance was not noticeably worse than the other methods tried on our equipment and data sets, and the payout in developer convenience for arbitrary slicing makes it worthwhile for us.

我没有严格地对它进行基准测试，而且我也不是大数据，所以您的里数可能会有所不同，但是性能并没有明显地比我们在设备和数据集上尝试的其他方法差，而且开发人员为任意分割提供的便利使我们值得这样做。

#1

169