How to group time by hour or by 10 minutes
like when I do
SELECT [Date] FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY [Date]
how can I specify the group period ?
MS SQL 2008
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 ?
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)
Read more... Read less...
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')
MINUTEterms can be changed to any number and
- It is a
DATETIMEvalue, which means:
- It works fine across long time intervals. (There is no collision between years.)
- Including it in the
SELECTstatement 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:
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
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.
In T-SQL you can:
SELECT [Date] FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY [Date], DATEPART(hh, [Date])
by minute use
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)
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.