在T-SQL中，您可以:

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

or

Date (mi， [Date])

or

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

间隔10分钟，你会

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

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

GROUP BY DATEPART(HOUR, [Date])

应该是这样的

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 

终于讲完了

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

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

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

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

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

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

MySql:

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

对于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 将该余数的负数与目标时间相加以获得切片时间:DATEADD(MILLISECOND， -@rms, time)

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

我没有严格地对其进行基准测试，我也不是大数据行业的人，所以您的情况可能会有所不同，但性能并不明显比我们在设备和数据集上尝试的其他方法差，而且任意切片在开发人员便利性方面的付出对我们来说是值得的。

短而甜

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.

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

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)

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

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

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

如果你想实际显示日期，有一个变量分组，并能够指定大于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

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

在SQLite中，为了按小时分组，你可以这样做:

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

并按每10分钟分组:

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

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