Блог

Мой первоначальный вопрос был слишком компактным и распространенным, поэтому я попытался очистить его здесь. Пытаюсь выяснить, как правильно внедрить DST в мой сценарий SQL для США.

a.ActualEnd = значение даты и времени amp; c.TimeZoneBias = минуты для смещения в зависимости от местного времени пользователей

ОБНОВЛЕНИЕ: весь сценарий ниже

 INSERT INTO [AS400].S062f7ar.APLUS83MDS.PEPAPPTS01  
(PPCONO, PPREP1, PPDATE, PPCOUNT)
select '1' as PPCONO, 
       b.new_SalesrepId as PPREP1, 
       MAX(CONVERT(varchar(8), 
       (a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float)), 112)) as PPDATE,       
       count(b.new_SalesrepId) as PPCOUNT 
from ActivityPointerBase as a
  join SystemUserExtensionBase as b 
   on b.SystemUserId = a.OwnerId
  join UserSettingsBase as c 
   on c.SystemUserId = b.SystemUserId
where b.new_SalesrepId <> '99999999' 
 and a.ActivityTypeCode = '4201' 
 and b.new_SalesrepId is not NULL 
 and a.StateCode = '1' 

 and CONVERT(varchar(8), 
             a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float), 
             112) >= dateadd(day,datediff(day,
                                            1,
                                          CONVERT(varchar(8), 
                                                  GetDate(), 
                                                  112)
                                         ),
                             0) 
 and CONVERT(varchar(8), 
             a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float), 
             112) < dateadd(day,datediff(day,
                                           0,
                                         CONVERT(varchar(8), 
                                                 GetDate(), 
                                                 112)
                                         ),
                             0)
 group by b.new_SalesrepId, 
   CONVERT(varchar(8), 
   (a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float)), 112)
 order by b.new_SalesrepId ASC;
 

Хотите правильно включить летнее время для США в этот оператор where. Я не хочу вручную менять сценарий каждый раз, когда наступает летнее время.

Предположения

Поскольку эти пункты явно не определены в вопросе, я излагаю здесь свои предположения о них.

1. Текущий сервер хранит все даты и время в базе данных UTC.
2. Запрос должен возвращать все даты и время по местному времени пользователя.
3. Запрос должен возвращать все записи в пределах диапазона дат, но в соответствии с местным временем каждого пользователя.
4. TimeZoneBias — это американизированное смещение в минутах, где значение 420 равно UTC- (420/60) или UTC-7 (MST), а значение -120 равно UTC- (-120 / 60) или UTC 2 (EET).

Решения

Я вижу три возможных решения. Я считаю, что все они могут быть осуществимы, предполагая, что сначала для нескольких из них выполняется небольшая миграция данных.

Решение 1: DATETIMEOFFSET

Внедрите столбец ActualEnd в качестве типа DATETIMEOFFSET в таблице ActivityPointerBase. Приложение должно будет предоставить временные метки с текущим локальным смещением пользователя. Затем просто используйте CONVERT(DATE, a.ActualEnd), чтобы позже получить локальную дату пользователя. Даже позволяет сравнивать временные метки между пользователями, поскольку база данных может неявно выполнять вычисления смещения при сравнении двух значений DATETIMEOFFSET. И есть функции для преобразования DATETIMEOFFSET в ДАТУ-время UTC для сравнения с другими таблицами.

 SELECT '1' as PPCONO,
       b.new_SalesrepId AS PPREP1,
       CONVERT(VARCHAR(8), a.ActualEnd, 112) AS PPDATE,
       COUNT(b.new_SalesrepId) AS PPCOUNT
FROM ActivityPointerBase AS a
JOIN SystemUserExtensionBase AS b ON b.SystemUserId = a.OwnerId
WHERE b.new_SalesrepId <> '99999999' 
AND a.ActivityTypeCode = '4201' 
AND b.new_SalesrepId IS NOT NULL 
AND a.StateCode = '1' 
AND CONVERT(DATE, a.ActualEnd) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))
GROUP BY b.new_SalesrepId,
         CONVERT(VARCHAR(8), a.ActualEnd, 112)
ORDER BY b.new_SalesrepId ASC;
 

Решение 2. Храните необходимые данные

Храните в базе данных как локальное время пользователя, так и время UTC. Поскольку вам, очевидно, потребуется пользовательское локальное время позже, сделайте его сохранение обязательным требованием вашей системы. Тогда у вас есть столбцы ActualEnd и UserActualEnd в таблице ActivityPointerBase.

 SELECT '1' as PPCONO,
       b.new_SalesrepId AS PPREP1,
       CONVERT(VARCHAR(8), a.UserActualEnd, 112) AS PPDATE,
       COUNT(b.new_SalesrepId) AS PPCOUNT
FROM ActivityPointerBase AS a
JOIN SystemUserExtensionBase AS b ON b.SystemUserId = a.OwnerId
WHERE b.new_SalesrepId <> '99999999' 
AND a.ActivityTypeCode = '4201' 
AND b.new_SalesrepId IS NOT NULL 
AND a.StateCode = '1' 
AND CONVERT(DATE, a.UserActualEnd) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))
GROUP BY b.new_SalesrepId,
         CONVERT(VARCHAR(8), a.UserActualEnd, 112)
ORDER BY b.new_SalesrepId ASC;
 

Решение 3. восстановить локальное время пользователя

Создайте систему, в которой вы можете восстанавливать локальное время пользователя из временных меток UTC. Ниже я изложу простой вариант, но он ни в коем случае не является проверенным и не является единственным способом их восстановления.

Во-первых, вам понадобится столбец ObserveDst в таблице UserSettingsBase, потому что некоторые состояния соответствуют летнему времени, а некоторые нет, поэтому данных TimeZoneBias недостаточно для восстановления локального времени пользователя.

   ObserveDst INT NOT NULL
 

Который будет содержать значение 1, если они соблюдают летнее время и 0 в противном случае.

Во-вторых, вам понадобится таблица временных меток начала и окончания летнего времени, поскольку со временем они меняли определения и, возможно, могли бы сделать это снова в будущем. Я предлагаю таблицу, которая разбита на интервалы. Таким образом, вы можете выполнять простые соединения с другими таблицами вместо определения и вызова функции, которая работает для каждой записи.

 CREATE TABLE Dst
(
  BeginDT DATETIME NOT NULL,
  EndDT DATETIME NOT NULL,
  DstBias INT NOT NULL,
  PRIMARY KEY(BeginDT)
);
 

И поместите индекс (BeginDT, EndDT, DstBias) в таблицу. В этом случае все в порядке, потому что это будет не очень большая таблица. Для 2014 года у вас будут следующие записи:

 ('2014-01-01 00:00:00', '2014-03-09 02:00:00', 0)
('2014-03-09 02:00:00', '2014-11-02 03:00:00', 60)
('2014-11-02 03:00:00', '2015-01-01 00:00:00', 0)
 

Ноябрьский час может показаться немного странным, но цель этой таблицы — перейти от местного времени, отличного от летнего, к местному времени летнего времени. И 2014-11-02 03:00:00 EST — это 2014-11-02 02:00:00 EDT. Кроме того, имейте в виду, что это интервалы между закрытием и открытием, что означает включение первой временной метки и вплоть до последней временной метки, но не включая ее. Если у вас есть исторические даты, предшествующие летнему времени, вы можете сжать их все в один интервал. Вы даже можете объединить начальные и конечные интервалы каждого года, что даст вам только N 3 записи за N лет.

Министерство транспорта Соединенных Штатов определяет федеральные даты для штатов, которые предпочитают переходить на летнее время (http://www.dot.gov/regulations/daylight-saving-time ). Я предлагаю вам обратиться к ним или на другой разумный официальный сайт для получения правильных дат за нужные вам годы.

Затем вы просто присоединяетесь к таблице Dst и выполняете базовые вычисления смещения.

 SELECT '1' as PPCONO,
       b.new_SalesrepId AS PPREP1,
       CONVERT(VARCHAR(8), CONVERT(DATE, DATEADD(MINUTE, d.DstBias*c.ObserveDst-c.TimeZoneBias, a.ActualEnd)), 112) AS PPDATE,
       COUNT(b.new_SalesrepId) AS PPCOUNT
FROM ActivityPointerBase AS a
JOIN SystemUserExtensionBase AS b ON b.SystemUserId = a.OwnerId
JOIN UserSettingsBase AS c ON c.SystemUserId = b.SystemUserId
JOIN Dst AS d ON DATEADD(MINUTE, -c.TimeZoneBias, a.ActualEnd) >= d.BeginDT AND DATEADD(MINUTE, -c.TimeZoneBias, a.ActualEnd) < d.EndDT
WHERE b.new_SalesrepId <> '99999999' 
AND a.ActivityTypeCode = '4201' 
AND b.new_SalesrepId IS NOT NULL 
AND a.StateCode = '1' 
AND CONVERT(DATE, DATEADD(MINUTE, d.DstBias*c.ObserveDst-c.TimeZoneBias, a.ActualEnd)) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))
GROUP BY b.new_SalesrepId,
         CONVERT(VARCHAR(8), CONVERT(DATE, DATEADD(MINUTE, d.DstBias*c.ObserveDst-c.TimeZoneBias, a.ActualEnd)), 112)
ORDER BY b.new_SalesrepId ASC;