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

TL;DR


  • Необходимо различать термины:
    • UTC — локальное время в зоне +00:00, без эффекта DST
    • DateTimeOffset — локальное время со смещением от UTC ±NN:NN, где смещением является базовое смещение от UTC без эффекта DST (в C# TimeZoneInfo.BaseUtcOffset)
    • DateTime — локальное время без информации о таймзоне (мы игнорируем признак Kind)
  • Разделение использования на внешнее и внутренее:
    • Входящие и исходящие данные через API, сообщения, файловые экспорты/импорты должны быть строго в UTC (тип DateTime)
    • Внутри системы данные храняться вместе со смещением (тип DateTimeOffset)
  • Разделение использования в старом коде на не-БД код (C#, JS) и БД:
    • не-БД код оперирует только с локальными значениями (тип DateTime)
    • БД работает с локальными значениями + смещение (тип DateTimeOffset)
  • Новые проекты (компоненты) используют DateTimeOffset.
  • В БД тип DateTime просто меняется на DateTimeOffset:
    • в типах полей таблиц
    • в параметрах хранимок
    • в коде фиксятся несовместимые конструкции
    • к пришедшему значению присоединяется информация о смещении (простая конкатенация)
    • перед отдачей в не-БД код значение приводится к локальному
  • Никаких изменений в не-БД коде
  • DST решается использованием CLR Stored Procedures (для SQL Server 2016 можно использовать AT TIME ZONE).


Теперь детальнее о преодоленных сложностях.

«Вшитые» стандартны IT индустрии


Потребовалось довольно много времени, чтобы избавить людей от страха хранить даты в локальном времени со смещением. Некоторое время назад, если спросить программиста с опытом: «Как поддержать таймзоны?» — единственным вариантом был: «Используй UTC, а конвертируй в локальное время только перед показом». Тот факт, что для нормальной работы все равно необходима дополнительная информация, такая, как смещение и названия таймзон, был спрятан под капотом реализации. С появлением DateTimeOffset такие детали вылезли наружу, но инертность «программистского опыта» не позволяет быстро согласиться с другим фактом: «Хранение локальной даты с базовым смещением от UTC» — это тоже самое, что хранение UTC. Еще один плюс использования DateTimeOffset повсеместно позволяет делегировать контроль за соблюдением таймзон .NET Framework и SQL Server, оставив для человеческого контроля только моменты ввода и вывода данных из системы. Под человеческим контролем я имею ввиду написанный программистом код для работы с date/time значениями.

Чтобы преодолеть подобный страх пришлось провести не одну сессию с разъяснениями, представляя примеры и Proof Of Concept. Чем проще и ближе примеры к тем задачам, которые решаются в проекте, тем лучше. Если пускаться в рассуждения «вообще», то это приводит к усложнению понимания и трате времени впустую. Коротко: ме��ьше теории — больше практики. Аргументы за UTC и против DateTimeOffset можно отнести к двум категориям:

  • «UTC all the time» является стандартом и остальное не работет
  • UTC решает проблему с DST

Следует отметить, что ни UTC, ни DateTimeOffset не решают проблему с DST без использования информации о правилах конвертации между зонами, которая доступна через класс TimeZoneInfo в C#.

Упрощенная Модель


Как выше отметил, в старом коде изменения происходят только в БД. Как именно это работает можно оценить на простом примере.

Пример модели в T-SQL
-- 1) сохранение данных
-- входящие данные в локали пользователя, как он их видит
declare @input_user1 datetime = '2017-10-27 10:00:00'
-- в конфигурации пользователя есть информация о зоне
declare @timezoneOffset_user1 varchar(10) = '+03:00'
 
declare @storedValue datetimeoffset
-- при получении значений присоединяем смещение пользователя
set @storedValue = TODATETIMEOFFSET(@input_user1, @timezoneOffset_user1)
-- это значение будет сохранено
select @storedValue 'stored'
 
-- 2) отображение информации
-- в конфигурации 2го пользователя указана другая таймзона
declare @timezoneOffset_user2 varchar(10) = '-05:00'
-- перед выдачей в клиентский код значения приводятся к локальным
-- так будут выглядеть данные в базе и на дисплеях пользователей
select
@storedValue 'stored value',
CONVERT(DATETIME, SWITCHOFFSET(@storedValue, @timezoneOffset_user1)) 'user1 Moscow',
CONVERT(DATETIME, SWITCHOFFSET(@storedValue, @timezoneOffset_user2)) 'user2 NY'
 
-- 3) теперь 2й пользователь сохраняет данные
declare @input_user2 datetime
-- на вход приходят локальные значения, как он их видит в Нью-Йорке
set @input_user2 = '2017-10-27 02:00:00.000'
 -- соединяем с информацией о смещении
set @storedValue = TODATETIMEOFFSET(@input_user2, @timezoneOffset_user2)
select @storedValue 'stored'
 
-- 4) отображение информации
select
@storedValue 'stored value',
CONVERT(DATETIME, SWITCHOFFSET(@storedValue, @timezoneOffset_user1)) 'user1 Moscow',
CONVERT(DATETIME, SWITCHOFFSET(@storedValue, @timezoneOffset_user2)) 'user2 NY'


Результат выполнения скрипта будет следующим.



По примеру видно, что данная модель позволяет делать изменения только в БД, что значительно уменьшает риск возникновения дефектов.

Примеры функций для обработки date/time значений
-- При получении значений из не-БД кода в DateTimeOffset они будут локальными, но со смещением +00:00, поэтому необходимо присоединить смещение юзера, но конвертировать между поясами нельзя. Для этого переведем значение в DateTime и потом обратно уже с указанием смещения
-- DateTime без проблем конвертируется в DateTimeOffset, поэтому изменять вызов хранимок в клиентском коде не надо

create function fn_ConcatinateWithTimeOffset(@dto datetimeoffset, @userId int)
returns DateTimeOffset as begin
    declare @user_time_zone varchar(10)
    set @user_time_zone = '-05:00' -- из настроек юзера @userId
    return todatetimeoffset(convert(datetime, @dto), @user_time_zone)
end

-- Клиентский код не может читать DateTimeOffset в переменные типа DateTime, поэтому необходимо не только сконвертировать в в нужную таймзону, но и привести к DateTime, иначе произойдет ошибка 

create function fn_GetUserDateTime(@dto datetimeoffset, @userId int)
returns DateTime as begin
    declare @user_time_zone varchar(10)
    set @user_time_zone = '-05:00' -- из настроек юзера @userId
    return convert(datetime, switchoffset(@dto, @user_time_zone))
end


Маленькие Артифакты


В ходе адаптации SQL кода были обнаружены некоторые вещи, которые работают для DateTime, но несовместимы с DateTimeOffset:

  • GETDATE()+1 надо заменить на DATEADD(day, 1, SYSDATETIMEOFFSET())
  • ключевое слово DEFAULT несовместимо с DateTimeOffset, надо использовать SYSDATETIMEOFFSET()
  • конструкция ISNULL(date_field, NULL) > 0" работает с DateTime, но для DateTimeOffset должна быть заменена «date_field IS NOT NULL»

Заключение или UTC vs DateTimeOffset


Кто-то может заметить, что как и в подходе с UTC мы занимаемся конвертацией при получении и при отдаче данных. Тогда зачем это все, если есть проверенное и работающее решение? Есть несколько причин этому:

  • DateTimeOffset позволяет забыть где находится SQL Server.
  • Это позволяет переложить часть работы на систему.
  • Конвертации можно свести к минимуму, если DateTimeOffset используется везде, делая их только перед отображением данных или выдачи их во внешние системы.

Эти причины нам п��казались существенными за использование описанного подхода. Буду рад ответить на вопросы, пишите в коментах.