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

这里有一个选项，提供了该间隔的人类可读的开始时间(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

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

短而甜

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.

MySql:

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

我知道我讲这个有点晚了，但我用了这个——非常简单的方法。这可以让你得到60分钟的切片，没有任何舍入问题。

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