You want to transform your system from one state to another. The initial state is when DateTime is used everywhere. The target state is when DateTimeOffset is used everywhere. You want to do it smoothly and introducing as little changes as possible. This might be a plot for the most interesting task to a dead end. DateTime type was the default .NET type to capture date/time values and usually the logic built around them was made like it will last forever. Changes of existing fields will produce cascadian changes with effect on almost all parts of the enterprise system. In some critical case you might end up with changing about 200 stored procedures only for one field. This is the first problem. The second problem is that the consequences of such changes are hard to be found during testing. The regression testing does not guarantee that you won’t miss anything or the system will be functional for any cases. The required QA efforts will be increasing as you work and you won’t have a clear understanding when it will end.
During my investigation I found a possible approach for such systems which has three steps. The approach is based on the assumption that the system is not currently support time zones and all subsystems are located in the same time zone.
- Add a paired computed field for reading DateTimeOffset values from DB.
- Make transformation around operations which READS date/time from DB in client code.
- Make transformation around operations which WRITES date/time from DB in client code.
This approach will help to localize the changes and limit QA efforts. It also provides good predictability for estimation of the future work. I described the steps with more technical details below.
Imagine there are about 150 fields related to date/time values. You can use the following SQL script to find out the complete list in your DB.
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
Whilst in DB the conversion from DateTime to DateTimeOffset and back is supported at very good level and is not a big deal, in C# code this is complicated because of the strong typing. You cannot read DateTime value if a DB routine returns DateTimeOffset value. When you change a returning type for one field then all places where it is used in the whole enterprise system should be modified. In some cases it’s just impossible because you may don’t know about some places if the system is very big. This is the reason the approach with just changing the type of field won’t work. You may try to find all usages of specific column using the following script.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%table%' OR ROUTINE_DEFINITION LIKE '%field%' AND ROUTINE_TYPE='PROCEDURE'
In order to do the transformation it’s important to predict upfront what parts of the system will be affected. You should have an approach to localize the changes in specific module of the system without breaking the system at all.
It’s just better, not the best one. I still expect some issues might appear in the future, but it’s comparatively more safe than the previous one. The main difference is that you’re not doing the transformation in one single step. There is a sequence of dependent changes which will give you the control over efforts.
Paired Computed Field
When you add a computed paired column to DB you’ll introduce a new field with required type. It will allow you to segregate reading, writing and separate updated code from the old one. This operation easily can be done with the script and no QA efforts required.
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
Based on the above result you can slice your system into sections where you’d like to introduce DateTimeOffset. Now you can use the new type only in a single stored procedure without need to change all related places.
Transformation of Reads
The reading operations appeared the most difficult for the transformation because of the approach which is used for integration between the client code and DB routines. Date/time values are passed via string serialization. DateTimeOffset has different format and cannot be read by default to DateTime variables on the client side. The write operations just work. If you pass DateTime value to DateTimeOffset argument or field the value will be accepted with the assumption that it’s UTC adjusted value. The time offset after conversion will be “+00:00”.
Now you may take some section of your system and find exact number of DB routines returning DateTime to the client code. Here you want to change read operations in the client code to read DateTimeOffset values. You also will need to change DB routines to make sure they return values from the new computed fields. The expected result of this step looks as follows:
- The client code reads DateTimeOffset and use this type wherever possible to expose values from the system.
- The DB routines use DateTimeOffset in arguments and the client code passes DateTimeOffset value to them.
- The new type is used internally in DB routines.
- DB routines return value from the computed fields.
In the end you’ll get the system which reads from new fields when stores values via the old one. At this moment once you capture the time offset in write operations the whole system will start to work correctly with time zones.
Transformation of Writes
Now your system should capture the time offset and send it to DB to store in the fields. Section by section, routine by routine you should take the old field and change it to be computed from the new one. You’re already reading from them, now you’re storing values and keep the old one for the backward compatibility. This approach will help you to isolate changes only for specific section. The expected result looks as follows:
- the client code creates DateTimeOffset values and passes them to DB routines
- the new fields are now real fields with values
- the old fields are now computed fields
- DB routines stores values to the new fields
In the end you’ll get the system which writes and reads new type DateTimeOffset. This type has built-in support of time offset so that you won’t need to do any manual conversion to UTC.
It’s up to you how to slice the system to provide isolation according to routines which they’re using. You’ll make all efforts predictable which help to estimate them upfront. This is no doubt still may introduce issues but they won’t grow like a snowball as you work. Later you can get rid of the old fields and even more introduce more source of time offsets. They might be user preferences or some other configurations. Below I put information about compatibility of two types for references.
- Changing of column type just works assuming values in UTC. The time offset +00:00 will be added implicitly. If you need to specify another time zone consider the solution with a temp column.
- Formatting just works.
- Comparison just works.
- SYSDATETIMEOFFSET() will just work instead of GETDATE()
- Any assignment from DateTimeOffset to DateTime and vise versa will just work.
|Convert DateTime to DateTimeOffset||TODATETIMEOFFSET(datetime_field, ‘-00:00’)||With UTC adjustment|
|Convert from DateTimeOffset to DateTime||CONVERT(DATETIME, datetimeoffset_field)
– or –
SET @datetime = @datetimeoffset
|The time zone information will be lost after that. The time offset will be ignored without adjusting to any time zone. ‘2017-04-05 10:02:00 +01:00’ will become ‘2017-04-05 10:02:00’.|
|Get current date/time||SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘+00:00’)||Returns UTC adjusted value|
|Built-in routines||DATEPART, DATEDIFF, BETWEEN, <, >, =, etc.||DATEDIFF, BETWEEN and operators capture the time offset if one argument is DateTimeOffset value, assuming DateTime value in UTC|
|Formatting||CONVERT(NVARCHAR, datetimeoffset_field, 103)||It will produce the same result as for DateTime.|