SQL 如何按小时或 10 分钟对时间进行分组

How to group time by hour or by 10 minutes

提问by cnd

like when I do

就像我做的时候

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

how can I specify the group period ?

如何指定组时间？

MS SQL 2008

微软 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。是否可以在没有毫秒的情况下进行 Date 输出？

回答by cnd

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)

回答by Michael - Where's Clay Shirky

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

我参加聚会超级迟到了，但这并没有出现在任何现有的答案中：

GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', date_column) / 10 * 10, '2000')

• The 10and MINUTEterms can be changed to any number and DATEPART, respectively.
• It is a DATETIMEvalue,which means:
  • It works fine across long time intervals. (There is no collision between years.)
  • Including it in the SELECTstatement will give your output a column with pretty output truncated at the level you specify.
• '2000'is an "anchor date" around which SQL will perform the date math. Jereonh discovered belowthat you encounter an integer overflow with the previous anchor (0) when you group recent dates by seconds or milliseconds.^†

• 该10和MINUTE条件是可以改变的任何号码并且DATEPART分别。
• 它是一个DATETIME值，这意味着：
  • 它可以在很长的时间间隔内正常工作。（年份之间没有碰撞。）
  • SELECT将它包含在语句中将为您的输出提供一个列，其中包含在您指定的级别截断的漂亮输出。
• '2000'是一个“锚定日期”，SQL 将围绕它执行日期数学运算。Jereonh在下面发现，0当您按秒或毫秒对最近的日期进行分组时，您会遇到前一个锚点 ( )的整数溢出。^†

SELECT   DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
                                                               AS [date_truncated],
         COUNT(*) AS [records_in_interval],
         AVG(aa.[value]) AS [average_value]
FROM     [friib].[dbo].[archive_analog] AS aa
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
ORDER BY [date_truncated]

If your data spans centuries,^‡using a single anchor date for second- or millisecond grouping will still encounter the overflow. If that is happening, you can ask each row to anchor the binning comparison to its own date's midnight:

如果您的数据跨越几个世纪，^‡使用单个锚定日期进行秒或毫秒分组仍会遇到溢出。如果发生这种情况，您可以要求每一行将分箱比较锚定到其自己日期的午夜：

• Use DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date]), 0)instead of '2000'wherever it appears above. Your query will be totally unreadable, but it will work.

• An alternative might be CONVERT(DATETIME, CONVERT(DATE, aa.[date]))as the replacement.

• 使用DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date]), 0)代替'2000'上面出现的任何地方。您的查询将完全不可读，但它会起作用。

• 替代方案可能是CONVERT(DATETIME, CONVERT(DATE, aa.[date]))替代品。

_{^†2³²≈ 4.29E+9, so if your DATEPARTis SECOND, you get 4.3 billion seconds on either side, or "anchor ± 136 years." Similarly, 2³²milliseconds is ≈ 49.7 days.
^‡If your data actually spans centuries or millenia and is stillaccurate to the second or millisecond… congratulations! Whatever you're doing, keep doing it.}

_{^†2 ³²≈ 4.29E+9，因此如果您DATEPART是SECOND，则两边各有43 亿秒，或“锚定 ± 136 年”。同样，2 ³²毫秒 ≈ 49.7 天。
^‡如果您的数据实际上跨越了几个世纪或几千年，并且仍然精确到秒或毫秒……恭喜！不管你在做什么，继续做下去。}

回答by tzup

In T-SQL you can:

在 T-SQL 中，您可以：

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

or

或者

by minute use DATEPART(mi, [Date])

按分钟使用 DATEPART(mi, [Date])

or

或者

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

使用 10 分钟DATEPART(mi, [Date]) / 10（就像 Timothy 建议的那样）

回答by Timothy Khouri

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])

回答by Frank Schmitt

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:

在甲骨文中：

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

回答by Derek

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，即半天等。

回答by nobilist

For MySql:

对于 MySQL：

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

回答by user3193141

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

回答by Rodas PT

declare @interval tinyint
set @interval = 30
select dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0), sum(Value_Transaction)
from Transactions
group by dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0)

回答by jesse

I know I am late to the show with this one, but I used this - pretty simple approach. This allows you to get the 60 minute slices without any rounding issues.

我知道我在这个节目中迟到了，但我使用了这个 - 非常简单的方法。这使您可以在没有任何舍入问题的情况下获得 60 分钟的切片。

Select 
   CONCAT( 
            Format(endtime,'yyyy-MM-dd_HH:'),  
            LEFT(Format(endtime,'mm'),1),
            '0' 
          ) as [Time-Slice]