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

Question

就像我做的那样

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

我怎样才能指定小组时间？我正在使用 MS SQL 2008。

我已经尝试过使用 % 10 和 / 10 。

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

是否可以使日期输出没有毫秒？

Answer 1

终于完成了

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

Answer 2

简短而温馨

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

，对 Derek 的回答表示衷心的感谢，它¹构成了本文的核心。 如果您使用的是 SQL Server 2022+，请直接访问Martin 的回答。

实际使用

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
-- WHERE aa.[date] > '1900-01-01'
GROUP BY DATEDIFF(MINUTE, '2000', aa.[date]) / 10
-- HAVING SUM(aa.[value]) > 1000
ORDER BY [date_truncated]

详细信息和额外注释

Bin 间隔大小

MINUTE 和 10 术语可以更改为任何DATEPART 和整数，^{2< /sup> 分别分组为不同的时间间隔。例如，10 和 MINUTE 表示十分钟间隔； 6 与 HOUR 是六个小时的间隔。}

如果您多次更改间隔，则将其声明为变量可能会受益。

DECLARE @interval int = 10;

SELECT   DATEADD(MINUTE, DATEDIFF(…) / @interval * @interval, '2000')
…
GROUP BY                 DATEDIFF(…) / @interval

值类型

分组的实际值是一组距 2000-01-01 00:00 的相对偏移量。这意味着长时间间隔的数据源是可以的。其他一些答案在年份之间存在冲突。

将 GROUP BY 表达式乘以间隔大小并将其包装在 DATEADD 调用中将返回一个 DATETIME 值。 将其包含在 SELECT 语句中将为您的输出提供带有截断时间戳的单列。请参阅上面的“实际用法”示例。

移动 bin 间隔的标签

DATEDIFF 之后的除法 (/) 运算将值截断为整数（FLOOR 快捷方式），从而产生 < SELECT 输出中每行的时间间隔的开始时间。

如果您想用其间隔的中间或结束来标记每一行，您可以使用以下命令调整DATEADD第二项中的划分下表中粗体部分：

标记为	查询修改	Credit to
End	…) / 10 * 10 + 10 , '2000' )	丹尼尔·埃尔金顿
中	…) / 10 * 10 + (10 / 2.0) , '2000')

如果您想向内舍入间隔，使每个时间戳代表其之前的一半间隔和之后的一半间隔，请使用类似以下内容：

DATEADD(MINUTE, ROUND(1. * DATEDIFF(MINUTE, '2000', date_column) / 10, 0) * 10, '2000')

注意 1. 来执行未截断的操作部门代替。您需要修改 GROUP BY 以进行匹配，并且您可能需要使用整个 ROUND(…) 表达式以避免任何意外的浮点舍入。

日期数学小知识

'2000' 是一个“锚定日期”，SQL 将围绕该日期执行日期数学。大多数示例代码使用 0 作为锚点，但是 JereonH 发现您在以下情况下遇到整数溢出：按秒或毫秒对最近的日期进行分组。³

如果您的数据跨越几个世纪，⁴在GROUP BY中使用单个锚定日期来表示秒否则毫秒仍然会遇到溢出。对于这些查询，您可以要求每一行将分箱比较锚定到其自己日期的午夜。

使用这两个替换之一来代替查询中出现的 '2000'：

• DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date]), 0)
• CONVERT(DATETIME, CONVERT(DATE, aa.[date]))

您的查询将完全不可读，但它会起作用。

---

_{¹ 在发布几年后我意识到我的代码可以简化为几乎与德里克的回答。
² 如果您希望所有 :00 时间戳都符合分箱条件，请使用您的 DATEPART 的最大值可以均匀划分为。⁵ 作为反例，将结果分组为 13 分钟或 37 小时的 bin 将跳过一些 :00，但是 它应该仍然可以正常工作。
³ 数学表示 2³² ≈ 4.29E+9。这意味着对于 SECOND 的 DATEPART，两边各有 43 亿秒，相当于“锚定日期 ± 136 年”。同样，2³² 毫秒约为 49.7 天。
⁴ 如果您的数据实际上跨越了几个世纪或几千年，并且仍然精确到秒或毫秒......恭喜！无论你在做什么，继续做下去。
⁵ 如果您想知道为什么我们的时钟顶部有一个 12，请思考5是6（12的一半）或以下的唯一整数不是 12 的因数。然后请注意 5 × 12 = 60。对于包含小时、分钟和秒的 bin 大小，您有很多选择。}

Answer 3

在 T-SQL 中，您可以：

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

或者

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

或

按 10 分钟使用 DATEPART(mi, [Date]) / 10 （例如蒂莫西建议）

Answer 4

对于 10 分钟的间隔，您可以

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

像 tzup 和 Pieter888 已经提到的那样...进行一个小时的间隔，只需

GROUP BY DATEPART(HOUR, [Date])

Answer 5

作者给出的原始答案效果很好。为了扩展这个想法，你可以做类似的事情

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

，这样你就可以按比 60 分钟更长的时间段进行分组，比如 720，即半天等。

Answer 6

应该是这样的

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

（对语法不是 100% 确定 - 我更像是 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 

Answer 7

对于 MySQL：

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

Answer 8

对于 SQL Server 2022+ 的用户，现在有一个专用的 DATE_BUCKET 函数可以满足此需求。

使用示例

DECLARE @ArchiveAnalog TABLE 
(
[Date] DATETIME2 PRIMARY KEY,
Value FLOAT
)

INSERT @ArchiveAnalog 
VALUES 
    ('2000-01-01 16:20:00.000', 1),  --Bucket 1
    ('2000-01-01 16:22:53.250', 2),
    ('2000-01-01 16:29:59.999', 3),
    ('2000-01-01 16:31:53.250', 4),  --Bucket 2
    ('2000-01-01 16:36:53.250', 5)


SELECT  
        DATE_BUCKET (minute, 10, [Date] ) AS BucketedValue,
        MIN([Date]) AS RecT, 
        COUNT(*) AS BucketCount,
        AVG(Value) AS BucketAvg
FROM @ArchiveAnalog
GROUP BY DATE_BUCKET (minute, 10, [Date] )
ORDER BY DATE_BUCKET (minute, 10, [Date] )

BucketedValue	RecT	BucketCount	BucketAvg
2000-01-01 16:20:00.0000000	2000-01-01 16:20:00.0000000	3	2
2000-01-01 16:30:00.0000000	2000-01-01 16:31： 53.2500000	2	4.5

如果基础列已编入索引，则也可以是 相当高效

Answer 9

如果您想实际显示日期，请使用变量分组，并能够指定大于 60 分钟的时间范围：

DECLARE @minutes int
SET @minutes = 90

SELECT
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Date]) / @minutes * @minutes, 0) as [Date],
    AVG([Value]) as [Value]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY
    DATEDIFF(MINUTE, 0, [Date]) / @minutes

Answer 10

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)

Answer 11

在 SQLite 中，为了按小时分组，您可以执行以下操作：

GROUP BY strftime('%H', [FRIIB].[dbo].[ArchiveAnalog].[Date]);

并按每 10 分钟分组：

GROUP BY strftime('%M', [FRIIB].[dbo].[ArchiveAnalog].[Date]) / 10;

Answer 12

我的解决方案是使用函数创建一个包含日期间隔的表，然后将该表连接到我想要使用表中的日期间隔进行分组的数据。
然后在呈现数据时可以轻松选择日期间隔。

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

Answer 13

对于 SQL Server 2012，虽然我相信它可以在 SQL Server 2008R2 中工作，但我使用以下方法将时间切片降低到毫秒：

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

其工作原理是：

• 获取固定点和目标时间之间的毫秒数：
  @ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)
• 取这些毫秒除以时间片后的余数：
  @rms = @ms % @msPerSlice code>
• 将余数的负数添加到目标时间以获得切片时间：
  DATEADD(MILLISECOND, -@rms, time)

不幸的是，由于微秒和更小的单位会溢出，因此，更大、更精细的数据集需要使用不太方便的定点。

我没有对此进行严格的基准测试，而且我也不从事大数据工作，因此您的里程可能会有所不同，但性能并没有比在我们的设备和数据集上尝试的其他方法明显差，而且开发人员可以方便地进行任意切片，这使得它值得对于我们来说。

Answer 14

select from_unixtime( 600 * ( unix_timestamp( [Date] ) % 600 ) ) AS RecT, avg(Value)
from [FRIIB].[dbo].[ArchiveAnalog]
group by RecT
order by RecT;

将两个 600 替换为您想要分组的任意秒数。

如果您经常需要此操作并且表不会更改，正如名称 Archive 所暗示的那样，将日期（和时间）转换并存储为表中的 unixtime 可能会更快一些。

Answer 15

我知道我在这个节目上迟到了，但我使用了这个 - 非常简单的方法。这样您就可以获得 60 分钟的切片，而不会出现任何舍入问题。

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

Answer 16

select dateadd(minute, datediff(minute, 0, Date), 0),
       sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)

Answer 17

尝试这个查询。它构成一列。 （参考@nobilist的回答）

GROUP BY CAST(DATE(`your_date_field`) as varchar) || ' ' || CAST(HOUR(`your_date_field`) as varchar) || ':' || CAST(FLOOR(minute(`your_date_field`) / 10) AS varchar) || '0' AS date_format

Answer 18

以下选项提供了该时间间隔的人类可读的开始时间（7:30、7:40 等）。

在临时表中，它使用 SMALLDATETIME 截断秒和毫秒，然后主查询减去所需分钟间隔内的任何数量。

SELECT DATEADD(MINUTE, -(DATEDIFF(MINUTE, '2000', tmp.dt) % 10), tmp.dt)
FROM (
    SELECT CAST(DateField AS SMALLDATETIME) AS dt
    FROM MyDataTable
) tmp

也可以用一行代码完成，但可读性较差。

SELECT DATEADD(MINUTE, -(DATEDIFF(MINUTE, '2000', CAST(DateField AS SMALLDATETIME)) % 10), CAST(DateField AS SMALLDATETIME)) AS [interval] FROM MyDataTable