Еще про внедрение таймзон в долгоживущий проект03.04.2017 00:18

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

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 используется везде, делая их только перед отображением данных или выдачи их во внешние системы.

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

Комментарии (10)

qw1

2 апреля 2017 в 09:46

+1

↑

↓

«Хранение локальной даты с базовым смещением от UTC» — это тоже самое, что хранение UTC
Нет, это больше, чем просто UTC. В России часто происходит дурдом с изменением зон в разных городах, типа давайте с нового года жить в UTC+03 вместо UTC+04, или давайте отменим летнее время, потом снова его введём, потом снова отменим.
Поэтому, например, для программы кадрового учёта, которая хранит время сотрудников на работу, UTC — лишние проблемы (если посмотреть на 2 года в прошлое, 5:00/UTC — это опоздание на час или приход вовремя?)
Конвертации можно свести к минимуму, если DateTimeOffset используется везде, делая их только перед отображением данных
Тут всё равно нужен анализ ситуаций. Иногда время 15:00 MSK показывать в Нью-Йорке как есть (с припиской «по Москве»), иногда конвертить в местное.
- ggrnd0
  
  2 апреля 2017 в 10:22 (комментарий был изменён)
  
  0
  
  ↑
  
  ↓
  
  Кроме того:
  
  Конвертации можно свести к минимуму, если DateTimeOffset используется везде, делая их только перед отображением данных или выдачи их во внешние системы.
  Ничего не изменилось, только вместо 2х полей в БД используется одно.
andreylartsev

2 апреля 2017 в 10:36

0

↑

↓

Таймзона это безусловно дополнительная информация ко времени события. Но описанный случай со временем прихода на работу является практически исключительным, и в этом случае я бы даже хранил таймзону как отдельное поле) чтобы всем было понятно что эта информация которую нельзя потерять при всяких конвертациях) Но если вам необходимо просто абсолютное время произошедшего события то таймзона лишняя информация которой просто не должно быть на уровне БД.
Используя принцип Оккама не умножать сущности без необходимости)
Вывод времени события в таймзоне пользователя относится к презентационной логике и конвертация в локальное время пользователя по моему скромному времени должна быть только там, а не в хранимых процедурах и не в каком либо t-sql.
То же самое относится к загрузкам данных, у разных источников данных могут быть разные соглашения о формате времени и таймзоне, но задачу конвертации в один общий формат и одну таймзону БД лучше решать именно там в каждом конкретном сервисе загрузки данных.
Кстати таймзона БД не обязательно должна быть UTC, иногда удобнее хранить данные в таймзоне локальной для основной команды разработки. Но главное чтобы к этой таймзоне не применялись правила DST. То есть если EST так уж EST никаких там ESD.
DexterHD

2 апреля 2017 в 12:49 (комментарий был изменён)

0

↑

↓

Что то мне подсказывает то все эти проблемы и изыскания от непонимания того, что такое «UTC» и что такое «Часовой пояс» (Timezone). А самое главное, как правильно это использовать.
единственным вариантом был: «Используй UTC, а конвертируй в локальное время только перед показом»

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

Для нормальной работы клиент (т.е. пользователь) должен так или иначе сообщить системе, в каком часовом поясе и в какой стране он находится. Иначе любые танцы с бубном не имеют ни какого смысла.
Чтобы не было никаких проблем достаточно 3 вещи:
1. Время везде хранись в UTC
2. Знать список правил, как устанавливать смещение относительно UTC (В зависимости от часового пояса, времени года, страны)
3. Знать Часовой пояс пользователя, а так же страну его местонахождения.
Если я не прав, рад бы был услышать возможные проблемы в данном подходе.
- qw1
  
  2 апреля 2017 в 13:15
  
  0
  
  ↑
  
  ↓
  
  Проблему я описал в первом комментарии. Она возникает, когда для события важно знать локальное время и показывать даты в часовой зоне события, а не в часовой зоне клиента, отображающего данные.
  - DexterHD
    
    2 апреля 2017 в 13:35
    
    0
    
    ↑
    
    ↓
    
    Этот конкретный юзкейс решается хранением данных о том, где и в каком часовом поясе это событие произошло.
    Причем для любого кол-ва событий произошедших географически в одном месте достаточно хранить всего 1 значение с информацией о том, где и в каком часовом поясе событие произошло.
    В физическом же смысле не важно где произошло событие, если оно произошло, то произошло одновременно во всех существующих часовых поясах.
    - qw1
      
      2 апреля 2017 в 19:26
      
      0
      
      ↑
      
      ↓
      
      для любого кол-ва событий произошедших географически в одном месте достаточно хранить всего 1 значение с информацией о том, где и в каком часовом поясе событие произошло
      Это неудобно, т.к. придётся к этому месту привязывать историю изменений его часового пояса. Гораздо прозрачнее текущую таймзону сохранять вместе с событием.
wildraid

2 апреля 2017 в 13:26

0

↑

↓

Главная проблема datatype’ов с поддержкой таймзон — отсутствие единого железного стандарта о том, как они должны работать. Как видим, даже внутри SQL Server поведение типа DateTimeOffset немного отличается от DateTime.
Что будет, если понадобится выгрузить эти данные для обработки в Hive, в BigQuery, в системы аналитики, в визуализаторы (Tableau etc.)? Что будет, если захотим поменять СУБД целиком?
justdev

2 апреля 2017 в 19:50

0

↑

↓

Спасибо за статью. Но все на самом деле еще сложнее и интереснее (немного больше подробностей тут Подводные камни Date & Time — Илья Фофанов).
corr256

2 апреля 2017 в 22:38

0

↑

↓

Где это вы использовали GETDATE ()+1 и ISNULL (date_field, NULL) > 0? oO