Получаем дату периода DateTime после полуночи - SQL

#sql #datetime

Вопрос:

У нас есть бизнес-правило, которое выполняется с 5 утра до 5 утра. Это означает, что в нашей базе данных, если у нас есть продажа, поступающая в ‘2019-01-05 02:00:00’, у этого есть ‘PeriodDate’ или 2019-01-04.

Я пытаюсь создать представление, которое получает сумму продаж за один день, но на данный момент я просто получаю стандартные 12:00-12:00 дня, используя следующий код.

 select Convert(date, dbo.Sales.[Time Stamp]) as PeriodDate, sum(Sales.[SalesAmount]) as SalesAmount, siteid from sales
group by Convert(date, dbo.Sales.[Time Stamp]), siteid

Что мне нужно сделать, чтобы сгруппировать продажи по правильной дате периода (с 5 утра до 5 утра).

примечание.b. Временная метка столбца — это столбец DateTime, поэтому я изначально преобразовал его в дату.

Ответ №1:

Вы можете вычесть 5 часов из [Time Stamp] , а затем преобразовать в date перед группировкой:

 select 
  convert(date, dateadd(hour, -5, [Time Stamp])) as PeriodDate, 
  sum([SalesAmount]) as SalesAmount, 
  siteid 
from sales
group by 
  convert(date, dateadd(hour, -5, [Time Stamp])), 
  siteid

1. Одна вещь, которая могла бы немного ускорить обработку, — это вложить ваш CONVERT() в подзапрос, и тогда вы сможете использовать агрегацию PeriodDate без необходимости CONVERT() повторять дважды.

2. Это идеально, спасибо @forpas. И я добавлю в подзапрос, чтобы ускорить время обработки Shawn. Спасибо команде

3. @JoshFletcher Два других замечания: 1) Используйте datetime2 вместо datetime . learn.microsoft.com/en-us/sql/t-sql/data-types/… 2) Если вы остаетесь с datetime , он округляется до 3 тысячных долей миллисекунды. Итак, в зависимости от того, как вы принимаете временную метку, если заказ поступает в 04:59:59.998 , он будет округлен до 05:00:00.000 и будет sum() отредактирован не в тот день.