Search code examples
sqlsql-servert-sqlsql-server-2016

SQL query that groups by one hour intervals starting from the current minute?

I have a table that has a Created column and a Code column. And data is being inserted into it every 1 minute with a recurring job, so it shouldn't have any date gaps.

I want to group it to 1 hour intervals, for the last 24 hours, starting from the current minute (when the query was executed - so I would get that minute with GETUTCDATE()) and then calculate the Percentage column for that group using a simple AVG(Percentage).

So far I've only managed to round down to the nearest hour, and my result set looks like this:

2023-03-01 15:00:00
2023-03-01 16:00:00
2023-03-01 17:00:00

This is achieved using this part of the query:

GROUP BY 
Code,
DATEADD(hour, DATEDIFF(hour, 0, Created), 0)

However, what I want to achieve is, if the current date is for example 2023-03-01 15:27:48, then I want the result set to be grouped from the minute, with 1 hour intervals. So it should look like this:

2023-03-01 15:27:00
2023-03-01 14:27:00
2023-03-01 13:27:00
2023-03-01 12:27:00
Solution

  • How about something like this:

    
select DATEADD(hour, datediff(hour, Created, GETDATE()), GETDATE())
, datediff(hour, Created, GETDATE())
, count(*)
from yourtable so
group by datediff(hour, Created, GETDATE())

    EDIT for minute-wise variant:

    
select  DATEADD(hour, datediff(hour, Created, currDate), currDate)
,   datediff(hour, Created, currDate), count(*)
from yourtable so
cross apply (
    select dateadd(minute, datediff(minute, 0, getdate()), 0) AS currDate
    ) cd
group by datediff(hour, Created, currDate),currDate