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 ?

Solution 1

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)

Solution 2

Short and sweet

GROUP BY DATEDIFF(MINUTE, '2000', date_column) / 10

With heavy acknowledgements to Derek's answer, which forms the core of this one.

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 DATEDIFF(MINUTE, '2000', aa.[date]) / 10
-- HAVING SUM(aa.[value]) > 1000
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 declaring it as a variable.

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

• Wrapping it with a DATEADD invocation with a multiplier will give you a DATETIME value, which means:

  • Data sources over long time intervals are fine. Some other answers have collision between years.
  • Including it in the SELECT statement will give your output a single column with the truncated timestamp.

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

_{¹ If you want all :00 timestamps to be eligible for binning, use an integer that your DATEPART's maximum can evenly divide into.⁴ As a counterexample, grouping results into 13-minute or 37-hour bins will skip some :00s, but it should still work fine.
² 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.}

Solution 3

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)

Solution 4

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

Solution 5

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.

View more solutions

Comments

[cnd]

GROUP BY [Date], DATEPART(hh, [Date]) is a mess , not ?

[tzup]

@nCdy Should be all right, otherwise you get an error "...invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

[cnd]

How do you group by whole date and by hours in the same time? I'm just using Min Date .

[tzup]

if you use Min(Date) then of course you can take out the Date in the Group By.

[cnd]

but here I group minutes of 1980 year with minutes of 2011 :S I need to care about it.

[Steven]

Would % be the correct math? Wouldn't that create 10 groups. For example: 1 % 10 = 9 2 % 10 = 8 It wouldn't even necessarily be the correct chronological grouping either. I think just a normal divide would be correct. Unless % isn't a remainder divide in sql.

[Tar]

Minutes divided by 10 will create grouping by 10 minute intervals, which i what's needed. Modulo 10 makes no sense.

[Tar]

Minutes divided by 10 will create grouping by 10 minute intervals, which i what's needed. Modulo 10 makes no sense.

[hdost]

I made it ROUND((DATEPART(MINUTE, DT.[Date]) / 5),0,1) * 5, so that when I look at the data it's correlated with the nearest time slot

[ozmo]

If possible please amend your answer to use "DATEPART(mi, [Date]) / 10" instead of the above. This initially gave me the impression that SQL interprets the '%' sign as something other than modulo. => SELECT (27 / 10) = 2 SELECT (27 % 10) = 7

[Biranchi]

What would be the MySQL query for the above ?

[Admin]

Year, month and day can be simplified to DATE(DT.[Date]).

[MikeMurko]

Agreed with Tar. The grouping does not make sense.

[Dan Parsonson]

@Keelan Doesn't work for me - however CONVERT(date, DT.[Date]) does.

[Daniel Elkington]

To round UP to the nearest 10 minutes you can do GROUP BY DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, date_column) / 10 * 10) + 10, 0) (and the same in the SELECT clause).

[affanBajwa]

datepart(hour, workingPolicy.workingHours)/2.0 gives 1.5 while datepart(hour, '1900-01-01 09:00:30.000')/2.0 gives 4.5 , i don't understand why? Note:workingPolicy.workingHours=1900-01-01 09:00:30.000. please help

[Simon.S.A.]

Welcome to stackoverflow. You need to fully explain your answer, and what this adds to the other 10 answers already on the site.

[jeroenh]

for SECOND as DATEPART I get an error message (The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.). It seems that MINUTE is the smallest datepart you can use with this approach.

[Michael]

@jeroenh, you are correct. I have added a section to talk about that. tldr: Change the 0 to '2000' (quotes are important!) and try SECOND again.

[howcheng]

@Michael This technique works great, but I did notice that if a given slice has a zero count, there is no corresponding row in the result set.

[Michael]

@howcheng Indeed. To do that, you could right-join against a table of all the possible intervals. You can generate one with a cursor, but I feel that's out of scope for the question.

[Shizzen83]

Please provide some explanations when you answer a question.

[Nicow]

Using a cursor will be very very slow on large data sets.

[Chingiz K.]

this one is definitely better than the accepted answer. one can also set an arbitrary date instead of 0, and make the resulting datediff smaller: DATEDIFF(minute, CAST('2021-08-01' AS datetime), [Date])/10

[Michael]

@ChingizK. The CAST can be implicit. DATEDIFF(MINUTE, '2021-08-01', [Date])/10 works fine.