Локальные даты и данные UTC в SQL Server

Отчёт о заказах за воскресенье требует сначала определить, в каком часовом поясе начинается это воскресенье. Значение UTC задаёт момент времени, но календарная дата зависит от зоны клиента или принятой бизнес-зоны. Постоянное смещение часто даёт правдоподобные суммы, однако теряет заказы при переводе часов. Прибавление 24 часов к уже вычисленному началу в UTC также может создать неправильный интервал.

Удобный контракт хранения: datetime2 содержит исключительно UTC, что отражено в имени OrderedAtUtc. Сам тип не хранит часовую зону и не проверяет соблюдение этого правила. Все источники записи обязаны следовать договорённости. datetimeoffset хранит смещение, но смещение не заменяет именованную зону с историческими правилами переходов.

Каждую границу преобразуем отдельно

Для локального дня 10 марта 2024 года в Денвере сначала создадим два значения местной полуночи. Затем независимо преобразуем каждое в UTC. В примере используется идентификатор зоны Windows; доступные на экземпляре имена следует проверить через sys.time_zone_info.

DECLARE @LocalDate date = '20240310';
DECLARE @Zone sysname = N'Mountain Standard Time';
DECLARE @StartLocal datetime2(0) = CONVERT(datetime2(0), @LocalDate);
DECLARE @EndLocal datetime2(0) = DATEADD(day, 1, @StartLocal);
DECLARE @StartUtc datetime2(0) = CONVERT(datetime2(0),
    @StartLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
DECLARE @EndUtc datetime2(0) = CONVERT(datetime2(0),
    @EndLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
SELECT @StartUtc AS StartUtc, @EndUtc AS EndUtc,
       DATEDIFF(hour, @StartUtc, @EndUtc) AS HoursInLocalDay;

Ожидаемое начало: 10 марта, 07:00 UTC. Конец: 11 марта, 06:00 UTC. Локальный день длится 23 часа. Слова "Standard Time" в имени не означают отсутствие правил летнего времени. Для локального 3 ноября 2024 года аналогичный расчёт даст 25 часов.

Первый AT TIME ZONE трактует datetime2 без смещения как местное время указанной зоны. Второй переводит полученный datetimeoffset в UTC. Только после этого можно убрать ставшее нулевым смещение и сравнивать результат со столбцом datetime2, содержащим UTC.

Исторические изменения правил в некоторых зонах затрагивают даже полночь. Если приложение принимает локальное время будущих встреч, нужно отдельно определить обработку несуществующих и повторяющихся часов. Автоматическое решение функции не обязательно соответствует бизнес-требованиям. Сохраняйте выбранный момент и исходный контекст, позволяющий объяснить выбор.

Полуоткрытый интервал сохраняет точность

Основной запрос напрямую сравнивает столбец с заранее подготовленными границами.

-- Assumes Orders.OrderedAtUtc contains UTC datetime2 values.
SELECT OrderId, OrderedAtUtc, Total
FROM dbo.Orders
WHERE OrderedAtUtc >= @StartUtc
  AND OrderedAtUtc < @EndUtc;

Нижняя граница включена, верхняя исключена. Заказ ровно в полночь следующего дня относится уже к следующему дню. Правило не зависит от точности datetime2 и не требует придумывать последнее время суток вроде 23:59:59.997.

Индекс, начинающийся с OrderedAtUtc, может поддерживать такой диапазон. Если каждый запрос дополнительно выбирает одного арендатора, полезнее может оказаться ключ TenantId, OrderedAtUtc. Решение зависит от реальных предикатов. Преобразование каждого сохранённого значения внутри WHERE обычно затрудняет прямой поиск по исходному индексу и повторяет вычисления для множества строк.

Типы параметров должны соответствовать типу столбца. Если данные хранятся в datetimeoffset, сохраняйте этот тип и у границ. Проверяйте также порядок начальной и конечной дат и ограничивайте случайно запрошенные огромные периоды. Для многодневного отчёта преобразуйте местную полночь после последнего выбранного дня, а не вычисляйте длительность умножением числа дней на 24.

Смысл времени важнее формата

Нельзя смешивать UTC и локальное серверное время в одном столбце. Позднее надёжно восстановить правило для каждой строки может оказаться невозможно, особенно в повторяющийся осенний час. Проверьте импорты, DEFAULT-ограничения и редко используемые пути записи до изменения отчётности.

Будущая встреча в 09:00 местного времени отличается от уже произошедшего события заказа. Законодательные правила зоны могут измениться. В зависимости от обещания продукта сохраняйте желаемую местную дату, время и имя зоны, а также определяйте момент пересчёта UTC. Для завершённых событий сохраняйте фактический момент.

Проверяйте обычные дни, оба сезонных перехода, строки точно на границах и клиентов из разных зон. Сравнивайте идентификаторы заказов, а не только суммы: две ошибки могут взаимно компенсироваться численно. Отдельно проверьте обмен с приложением, чтобы драйвер или сериализатор не добавлял ещё одно преобразование. Корректная работа со временем требует единого контракта от записи до отображения.

Техническая документация: Microsoft Learn: AT TIME ZONE · Microsoft Learn: datetime2.