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

Source: https://stackoverflow.com/questions/5002661
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]