How to group time by hour or by 10 minutes

Question

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)

Short and sweet

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

Practical usage

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 DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
ORDER BY [date_truncated]

Details and commentary

• The MINUTE and 10 terms can be changed to any DATEPART and integer,¹ 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 DECLAREing it as a variable.

    DECLARE @interval int = 10;
    …
    GROUP BY DATEADD(MINUTE, … / @interval * @interval, '2000')
    

• It is a DATETIME value, which means:

  • Long time intervals are fine. Some other answers have collision between years.
  • Including it in the SELECT statement will give your output a column that has pretty output truncated at the level you specify.

• The truncating integer division (a FLOOR shortcut) makes the date output shown in a SELECT the beginning of each time interval. If you want the middle or end of the 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').

Trivia

'2000' is an "anchor date" around which SQL will perform the date math. Most sample code uses 0 for the anchor, but JereonH discovered that you encounter an integer overflow when grouping more-recent dates by seconds or milliseconds.²

If your data spans centuries,³ using a single anchor date in the GROUP BY for seconds or milliseconds will still encounter the overflow. For those queries, 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.

_{¹ Technically, you can use any integer that the maximum for that DATEPART can divide into.⁴ If you want to group your results into 13-minute bins or 37-hour ones, you may end up with unequally-filled bins or time-shifted results.
² The math says 2³² ≈ 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, 2³² milliseconds is ≈ 49.7 days.
³ 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.
⁴ 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.}

In T-SQL you can:

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

or

by minute use DATEPART(mi, [Date])

or

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

For a 10 minute interval, you would

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

As was already mentioned by tzup and Pieter888... to do an hour interval, just

GROUP BY DATEPART(HOUR, [Date])

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)

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 

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.