Group by 24hr Period starting at 7:00 AM

Question

I have some high frequency data that I need to group by 24hr period starting at 7:00 AM. How can I do that?

For example, there is a timestamp every 5 minutes and I want to group it by day, but each day starts at 7:00 AM.

How would I go about doing that?

SQL Server 2016.

show your data, code yu tried etc. Look also https://stackoverflow.com/help/how-to-ask — cccnrc, Jul 31 '19 at 02:43
http://sqlfiddle.com/ would be perfect so we could test :) if you apply what you have so far there — Lasse Edsvik, Jul 31 '19 at 02:46
maybe this has some answers: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/62cb5ba8-2ea4-40ce-9fba-9abbe505bd92/group-by-a-specified-time-interval?forum=transactsql you need to group by time period — Lasse Edsvik, Jul 31 '19 at 02:48

score 0 · Accepted Answer · edited Jul 31 '19 at 03:49

I am going to presume your time is probably in UTC. I would look at converting the time to the timezone you need. Then it gives you more options of how you want to deal with the data, also you could customize the report for people in different zones. For some info about timezones I found this stackoverflow post.

Convert datetime value from one timezone to UTC timezone using sql query

Something like this in an group by clause should work. The code below says if the hour is under 8 then it's still yesterday, else it's really today. You would need to replace the getdate() with the correct column. If I Am correct about that the times are in UTC/GMT I would recommend looking at just converting it to the timezone you want.

case when datepart(hh, getdate())<8 then
cast((DATEADD(day, -1, getdate())) as date)
else
    cast(getdate() as date)
end

Group by 24hr Period starting at 7:00 AM

1 Answers1