Advertisement
Advertisement

## How to group time by hour or by 10 minutes

### Question

like when I do

``````SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date]
``````

how can I specify the group period ?

MS SQL 2008

2nd Edit

I'm trying

``````SELECT MIN([Date]) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY (DATEPART(MINUTE, [Date]) / 10)
ORDER BY RecT
``````

changed %10 to / 10. is it possible to make Date output without milliseconds ?

2012/04/17
1
172
4/17/2012 8:16:28 PM

### Accepted Answer

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)
``````
2012/08/28
218
8/28/2012 8:38:23 AM

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')
``````
• The `10` and `MINUTE` terms can be changed to any number and `DATEPART`, respectively.
• It is a `DATETIME` value, which means:
• It works fine across long time intervals. (There is no collision between years.)
• Including it in the `SELECT` statement will give your output a column with pretty output truncated at the level you specify.
• `'2000'` is an "anchor date" around which SQL will perform the date math. Jereonh discovered below that you encounter an integer overflow with the previous anchor (`0`) when you group recent dates by seconds or milliseconds.
``````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
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
ORDER BY [date_truncated]
``````

If your data spans centuries, using a single anchor date for second- or millisecond grouping will still encounter the overflow. If that is happening, 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.

232 ≈ 4.29E+9, so if your `DATEPART` is `SECOND`, you get 4.3 billion seconds on either side, or "anchor ± 136 years." Similarly, 232 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.

2019/09/09

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)

2016/06/03

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])
``````
2017/11/22

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
``````
2013/10/23

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.

2017/11/30

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]