Переход от DateTime к DateTimeOffset
Представим, что вы хотите преобразовать свою систему из одного состояния в другое. Начальное состояние — это когда DateTime используется везде, в C# коде и в БД. Конечное состояние — когда везде используется DateTimeOffset. Вы хотите сделать переход плавно и внести как можно меньше изменений. Это описание может быть началом очень интересной задачи с тупиком в конце.
Тип DateTime был типом .NET по умолчанию для работы с датой и временем некоторое время назад, и обычно построенная вокруг него логика была сделана так, как будто она никогда не поменяется. Если попытаться менять тип в один этап, это приведёт к каскадным изменениям практически во всех частях системы. В крайних случаях может потребоваться изменить около 200 хранимых процедур только для одного поля. Это первая проблема. И вторая проблема заключается в том, что последствия таких изменений трудно найти во время тестирования. Регрессионное тестирование не гарантирует, что вы ничего не упустите, или система будет функционировать в любых случаях. Необходимые усилия по обеспечению качества будут увеличиваться по мере работы, и у вас не будет четкого понимания, когда оно закончится.
Такой подход поможет локализовать изменения и ограничить усилия QA. Он также обеспечит хорошую предсказуемость для оценки будущей работы. Ниже я описал этапы более подробно.
Представьте, что существует около 150 полей, связанных со значениями даты/времени. Вы можете использовать следующий сценарий SQL, чтобы узнать полный список в вашей БД.
В то время как в БД преобразование из DateTime в DateTimeOffset и обратно поддерживается на очень хорошем уровне, в C# коде это сложно из-за типизации. Вы не можете прочитать значение DateTime, если БД возвращает значение DateTimeOffset. При изменении возвращаемого типа для одного поля необходимо изменить все места, где он используется во всей системе. В некоторых случаях это просто невозможно, потому что вы можете не знать о некоторых местах, если система очень большая. Именно по этой причине подход с простым изменением типа поля не будет работать. Вы можете попробовать найти все использования определенного поля таблицы в БД, используя следующий скрипт.
Для того, чтобы сделать трансформацию, важно заранее предсказать, какие части системы будут затронуты. У вас должен быть подход к локализации изменений в конкретном модуле системы без нарушения остальных частей.
Это подход просто «получше», а не лучший. Я все еще ожидаю, что некоторые проблемы могут появиться в будущем, но он выглядит более безопасным, чем предыдущий. Главное отличие состоит в том, что вы не выполняете преобразование за один шаг. Существует последовательность зависимых изменений, которая даст вам контроль над ситуацией.
Когда вы добавляете вычисляемое дублирующее поле в БД, вы вводите новое поле с требуемым типом. Это позволит вам отделить чтение, запись и отделить обновленный код от старого. Эта операция легко может быть выполнена с помощью скрипта, и никаких усилий по обеспечению качества не требуется.
Основываясь на приведенном выше результате, вы можете нарезать свою систему на разделы, где вы хотите ввести DateTimeOffset. Теперь вы можете использовать новый тип только в одной хранимой процедуре без необходимости изменять все связанные с ним места.
Операции чтения оказались наиболее сложными для преобразования из-за подхода, который используется для интеграции между клиентским кодом и БД. Значения даты/времени передаются посредством сериализации строк. DateTimeOffset имеет другой формат и не может быть прочитан по умолчанию для переменных DateTime на стороне клиента. В тоже время, операции записи просто работают. Если вы передадите значение DateTime аргументу или полю DateTimeOffset, это значение будет принято с предположением, что оно является скорректированным к UTC. Смещение времени после преобразования будет »+00:00».
В итоге вы получите систему, которая считывает данные из новых полей, в то время, как сохраняет значения в старые. Теперь, как только смещение времени будет передаваться в операциях записи и сохраняться в системе, вся система начнет корректно работать с часовыми поясами.
Теперь надо фиксировать смещение времени в системе, отправлять его в БД и сохранять в полях. Надо взять старое поле и изменить его на вычисляемое из нового, а новое должно теперь содержать значения. Вы уже читаете их них, теперь еще и записываете значения, а старые — наоборот, только чтение. Этот подход поможет вам изолировать изменения только для определенного раздела. Ожидаемый результат выглядит следующим образом:
В итоге вы получите систему, которая записывает и читает новый тип DateTimeOffset. Этот тип имеет встроенную поддержку смещения по времени, поэтому вам не нужно будет делать какое-либо ручное преобразование к UTC или между временными зонами, в общем случае.
Единственную рекомендацию, которую я могу дать относительно деления системы на секции для преобразования следующая: необходимо предусмотреть достаточную изолированность модулей в соответствии с используемыми хранимками. Таким образом, вы добьетесь предсказуемости усилий и сможете их оценить заранее. Несомненно, какие-то проблемы все еще могут возникнуть, но они не будут расти, как снежный ком по мере работы. Позже вы сможете избавиться от старых полей. Информацию о тайм зоне можно брать из операционной системы или настроек пользователя. Ниже я разместил информацию о совместимости двух типов в БД.
Очень интересно услышать истории о подобной трансформации от тех, кто уже проделывал такой в своих системах. А так же, кто и как поддерживает таймзоны в своих системах.
Тип DateTime был типом .NET по умолчанию для работы с датой и временем некоторое время назад, и обычно построенная вокруг него логика была сделана так, как будто она никогда не поменяется. Если попытаться менять тип в один этап, это приведёт к каскадным изменениям практически во всех частях системы. В крайних случаях может потребоваться изменить около 200 хранимых процедур только для одного поля. Это первая проблема. И вторая проблема заключается в том, что последствия таких изменений трудно найти во время тестирования. Регрессионное тестирование не гарантирует, что вы ничего не упустите, или система будет функционировать в любых случаях. Необходимые усилия по обеспечению качества будут увеличиваться по мере работы, и у вас не будет четкого понимания, когда оно закончится.
Во время моего исследования я нашел возможный подход к таким преобразованиям. Этот подход имеет три этапа и основан на предположении, что в настоящее время система не поддерживает временные зоны, а все подсистемы расположены в одном часовом поясе.
- Добавить парное вычисляемое поле для чтения значений DateTimeOffset из БД.
- Сделать преобразование операций чтения.
- Сделать преобразование операций записи.
Такой подход поможет локализовать изменения и ограничить усилия 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. |
Очень интересно услышать истории о подобной трансформации от тех, кто уже проделывал такой в своих системах. А так же, кто и как поддерживает таймзоны в своих системах.
