T- Sql Group by n hours

Question

It's possible to simple group by n hours? I found related questions for minutes but I cant do it with hours.

I need to group some data by 8 hours with offset.

So the first 8 hours are not 00:00 - 08:00 but 22:00 (yesterday) - 06:00 (today).

I grouped this by hour and stuck. How can I do that? Any advice is appreciated.

Answer 1

You can use the DATEADD/DATEDIFF trick to group by 8 hour intervals.

You need to add an offset though, in this case

• Add 2 hours to shift 22:30 to 00:30. Or 07:30 back to 09:30
• Group by the 8 hour interval (0-8, 8-16, 8-24)
• Subtract 2 to shift the rounded 00:00 back to 22:00, or the rounded 08:00 to 06:00

Example script

DECLARE @foo table (foo smalldatetime, bar int);

INSERT @foo (foo, bar)
VALUES
    ('2017-10-11 21:00', 1),
    ('2017-10-11 22:00', 2),
    ('2017-10-11 23:00', 3),
    ('2017-10-12 01:00', 4),
    ('2017-10-12 03:00', 5),
    ('2017-10-12 05:00', 6),
    ('2017-10-12 07:00', 7),
    ('2017-10-12 08:00', 8);

SELECT 
    SUM(bar), 
    DATEADD(hh, ((DATEDIFF(hh, 0, foo)+2)/8*8)-2, 0)
FROM 
    @foo 
GROUP BY 
    DATEADD(hh, ((DATEDIFF(hh, 0, foo)+2)/8*8)-2, 0);