SQL - Best way to GroupByDay from 6AM to 6AM

Question

I need to query a large number of rows (containing a timestamp column) and aggregate the results by day. The trick is I need the aggregate functions to be grouped for each day from 6AM until next day at 6AM, not from midnight to midnight.

Obviously, I can do some sort of "group by DATEPART(day,Timestamp-6 hours)" but for millions of rows this seems to add quite a bit of work to the server. Actually, it will slow the query from a couple of seconds to over 2 minutes and will eventually timeout.

What is a better way of doing this?

Answer 1

Can't you use the AT TIME ZONE to shift midnight w/o the expensive calculation?

Answer 2

The T-SQL below will do what you want in SQL Server. However, performance will not be great over a large number of rows as no index can be used due to the functions wrapping the dt column. For performance reasons you could consider adding a new computed column to your table to store the date with the time offset (-6 hours). This new column could then be indexed and performance should be fine.

if exists (select * from sys.objects WHERE object_id = object_id(N'dbo.t') AND type in (N'U'))
drop table dbo.t

create table dbo.t (dt datetime not null)

insert into dbo.t values ('20100819 05:00')
insert into dbo.t values ('20100819 07:00')
insert into dbo.t values ('20100819 23:00')
insert into dbo.t values ('20100820 04:00')
insert into dbo.t values ('20100820 11:00')
insert into dbo.t values ('20100820 13:00')
insert into dbo.t values ('20100821 00:45')

select convert(date,dateadd(HH,-6,dt)) as [Date],
COUNT(dt) as [Count]
from dbo.t
group by convert(date,dateadd(HH,-6,dt))