How to group time by hour or by 10 minutes

finally done with

GROUP BYDATEPART(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 issix hour intervals.
  • If you change the interval a lot, you might benefit from DECLAREing it as a variable.

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