Переход от DateTime к DateTimeOffset

Представим, что вы хотите преобразовать свою систему из одного состояния в другое. Начальное состояние — это когда DateTime используется везде, в C# коде и в БД. Конечное состояние — когда везде используется DateTimeOffset. Вы хотите сделать переход плавно и внести как можно меньше изменений. Это описание может быть началом очень интересной задачи с тупиком в конце.

Тип DateTime был типом .NET по умолчанию для работы с датой и временем некоторое время назад, и обычно построенная вокруг него логика была сделана так, как будто она никогда не поменяется. Если попытаться менять тип в один этап, это приведёт к каскадным изменениям практически во всех частях системы. В крайних случаях может потребоваться изменить около 200 хранимых процедур только для одного поля. Это первая проблема. И вторая проблема заключается в том, что последствия таких изменений трудно найти во время тестирования. Регрессионное тестирование не гарантирует, что вы ничего не упустите, или система будет функционировать в любых случаях. Необходимые усилия по обеспечению качества будут увеличиваться по мере работы, и у вас не будет четкого понимания, когда оно закончится.

Во время моего исследования я нашел возможный подход к таким преобразованиям. Этот подход имеет три этапа и основан на предположении, что в настоящее время система не поддерживает временные зоны, а все подсистемы расположены в одном часовом поясе.

1. Добавить парное вычисляемое поле для чтения значений DateTimeOffset из БД.
2. Сделать преобразование операций чтения.
3. Сделать преобразование операций записи.

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

Неудачный Подход

Представьте, что существует около 150 полей, связанных со значениями даты/времени. Вы можете использовать следующий сценарий SQL, чтобы узнать полный список в вашей БД.

select
    tbl.name as 'table',
    col.name as 'column',
    tp.name as 'type',
    def.name as 'default'
from sys.columns col
    inner join sys.tables tbl on tbl.[object_id] = col.[object_id]
    inner join sys.types tp on tp.system_type_id = col.system_type_id
        and tp.name in ('datetime', 'date', 'time', 'datetime2', 'datetimeoffset', 'smalldatetime')
    left join sys.default_constraints def on def.parent_object_id = col.[object_id]
        and def.parent_column_id = col.column_id
order by tbl.name, col.name

В то время как в БД преобразование из DateTime в DateTimeOffset и обратно поддерживается на очень хорошем уровне, в C# коде это сложно из-за типизации. Вы не можете прочитать значение DateTime, если БД возвращает значение DateTimeOffset. При изменении возвращаемого типа для одного поля необходимо изменить все места, где он используется во всей системе. В некоторых случаях это просто невозможно, потому что вы можете не знать о некоторых местах, если система очень большая. Именно по этой причине подход с простым изменением типа поля не будет работать. Вы можете попробовать найти все использования определенного поля таблицы в БД, используя следующий скрипт.

SELECT
    ROUTINE_NAME,
    ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%table%'
    OR ROUTINE_DEFINITION LIKE '%field%'
    AND ROUTINE_TYPE='PROCEDURE'

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

Подход «Получше»

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

Создание Вычисляемого Поля

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

declare @table sysname, @column sysname, @type sysname, @default sysname

declare cols cursor for
select
    tbl.name as 'table',
    col.name as 'column',
    tp.name as 'type',
    def.name as 'default'
from sys.columns col
    inner join sys.tables tbl on tbl.[object_id] = col.[object_id]
    inner join sys.types tp on tp.system_type_id = col.system_type_id
        and tp.name in ('datetime', 'date', 'time', 'datetime2', 'smalldatetime')
    left join sys.default_constraints def on def.parent_object_id = col.[object_id]
        and def.parent_column_id = col.column_id
order by tbl.name, col.name

open cols
fetch from cols into @table, @column, @type, @default
while @@FETCH_STATUS = 0
begin
    declare @cmd nvarchar(max)
    set @cmd = 'alter table ['+@table+'] add ['+@column+'_dto] as todatetimeoffset(['+@column+'], ''+00:00'')'
    exec (@cmd)
    fetch from cols into @table, @column, @type, @default
end
close cols
deallocate cols

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

Трансформация Чтения

Операции чтения оказались наиболее сложными для преобразования из-за подхода, который используется для интеграции между клиентским кодом и БД. Значения даты/времени передаются посредством сериализации строк. DateTimeOffset имеет другой формат и не может быть прочитан по умолчанию для переменных DateTime на стороне клиента. В тоже время, операции записи просто работают. Если вы передадите значение DateTime аргументу или полю DateTimeOffset, это значение будет принято с предположением, что оно является скорректированным к UTC. Смещение времени после преобразования будет «+00:00».

Теперь можно взять какой-то раздел системы и определить точное количество хранимок, возвращающих DateTime в код клиента. Тут надо будет изменить операции чтения в коде C# для чтения значений DateTimeOffset. Также потребуется изменить сами хранимки в БД, чтобы они возвращали значения из новых вычисляемых полей. Ожидаемый результат этого шага выглядит следующим образом:

• C# код считывает DateTimeOffset и использует этот тип везде, где возможно, чтобы возвращать значения из системы.
• Хранимки БД используют DateTimeOffset в аргументах, и C# код передает им значение DateTimeOffset.
• Новый тип используется внутри хранимок БД.
• Хранимки БД возвращают значения из новых добавленных полей.

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

Трансформация Записи

Теперь надо фиксировать смещение времени в системе, отправлять его в БД и сохранять в полях. Надо взять старое поле и изменить его на вычисляемое из нового, а новое должно теперь содержать значения. Вы уже читаете их них, теперь еще и записываете значения, а старые — наоборот, только чтение. Этот подход поможет вам изолировать изменения только для определенного раздела. Ожидаемый результат выглядит следующим образом:

• Код C# создает значения DateTimeOffset и передает их в БД
• Новые поля теперь являются реальными полями со значениями
• Cтарыt поля теперь вычисляемые и используются для чтения
• Хранимки БД сохраняют значения в новые поля

В итоге вы получите систему, которая записывает и читает новый тип DateTimeOffset. Этот тип имеет встроенную поддержку смещения по времени, поэтому вам не нужно будет делать какое-либо ручное преобразование к UTC или между временными зонами, в общем случае.

Дальнейшие Шаги

Единственную рекомендацию, которую я могу дать относительно деления системы на секции для преобразования следующая: необходимо предусмотреть достаточную изолированность модулей в соответствии с используемыми хранимками. Таким образом, вы добьетесь предсказуемости усилий и сможете их оценить заранее. Несомненно, какие-то проблемы все еще могут возникнуть, но они не будут расти, как снежный ком по мере работы. Позже вы сможете избавиться от старых полей. Информацию о тайм зоне можно брать из операционной системы или настроек пользователя. Ниже я разместил информацию о совместимости двух типов в БД.

• Изменение типа столбца из DateTime в DateTimeOffset работает с неявным преобразованием. Смещение времени будет +00:00. Если нужно указать другой часовой пояс, необходимо использовать временный столбец.
• Форматирование значений в строку поддержано.
• Сравнение всеми операторами поддержано.
• SYSDATETIMEOFFSET() без риска может заменить GETDATE()
• Любое присвоение между DateTime и DateTimeOffset работает с неявным преобразованием.

Операция	T-SQL	Комментарий
Конвертация DateTime в DateTimeOffset	TODATETIMEOFFSET(datetime_field, '+00:00')	Получите значение с добавленным смещением +00:00
Конвертация DateTimeOffset в DateTime	CONVERT(DATETIME, datetimeoffset_field) — or — SET @datetime = @datetimeoffset	Информация о смещении будет потеряна. При преобразовании смещение будет просто проигнорировано. Например, для '2017-04-05 10:02:00 +01:00' получите '2017-04-05 10:02:00'.
Текущее дата/время	SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00')	Это две команды. Результатом будет точка в UTC зоне
Встроенные операции	DATEPART, DATEDIFF, BETWEEN, <, >, =, etc.	DATEDIFF, BETWEEN и операции сравнения учитывают временное смещение, при этом DateTime значение представляется, как значение со смещением UTC
Форматирование	CONVERT(NVARCHAR, datetimeoffset_field, 103)	Получите тот же результат что и для DateTime.

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

Let's block ads! (Why?)