间隔10分钟，你会

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

正如tzup和Pieter888已经提到的……做一个小时的间隔

GROUP BY DATEPART(HOUR, [Date])

作者最初给出的答案还不错。为了扩展这个想法，你可以这样做

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

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

试试这个查询。它是一列。(参考@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

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并将其存储可能会更快一些。

在T-SQL中，您可以:

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

or

Date (mi， [Date])

or

使用DATEPART(mi， [Date]) / 10(像Timothy建议的那样)

短而甜

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

感谢德里克的回答，这构成了这篇文章的核心。

实际使用情况

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]

详情及评论

The MINUTE and 10 terms can be changed to any DATEPART and integer,1 respectively, to group into different time intervals. e.g. 10 with MINUTE is ten minute intervals; 6 with HOUR is six hour intervals. If you change the interval a lot, you might benefit from declaring it as a variable. DECLARE @interval int = 10; SELECT DATEADD(MINUTE, DATEDIFF(…) / @interval * @interval, '2000') … GROUP BY DATEDIFF(…) / @interval Wrapping it with a DATEADD invocation with a multiplier will give you a DATETIME value, which means: Data sources over long time intervals are fine. Some other answers have collision between years. Including it in the SELECT statement will give your output a single column with the truncated timestamp. In the SELECT, the division (/) operation after DATEDIFF truncates values to integers (a FLOOR shortcut), which yields the beginning of time intervals for each row. If you want to label each row with the middle or end of its interval, you can tweak the division in the second term of DATEADD with the bold part below: End of interval: …) / 10 * 10 + 10 , '2000'), credit to Daniel Elkington. Middle of interval: …) / 10 * 10 + (10 / 2.0) , '2000').

琐事

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

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

使用DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date])， 0)来代替上面出现的'2000'。您的查询将完全不可读，但它将工作。 另一种替代方法可能是CONVERT(DATETIME, CONVERT(DATE, aa.[DATE]))作为替换。

1 If you want all :00 timestamps to be eligible for binning, use an integer that your DATEPART's maximum can evenly divide into.4 As a counterexample, grouping results into 13-minute or 37-hour bins will skip some :00s, but it should still work fine. 2 The math says 232 ≈ 4.29E+9. This means for a DATEPART of SECOND, you get 4.3 billion seconds on either side, which works out to "anchor date ± 136 years." Similarly, 232 milliseconds is ≈ 49.7 days. 3 If your data actually spans centuries or millenia and is still accurate to the second or millisecond… congratulations! Whatever you're doing, keep doing it. 4 If you ever wondered why our clocks have a 12 at the top, reflect on how 5 is the only integer from 6 (half of 12) or below that is not a factor of 12. Then note that 5 × 12 = 60. You have lots of choices for bin sizes with hours, minutes, and seconds.