Transition from DateTime to DateTimeOffset
Imagine that you want to transform your system from one state to another. The initial state is when DateTime is used everywhere, in C # code and in the database. The end state is when DateTimeOffset is used everywhere. You want to make the transition smoothly and make as few changes as possible. This description can be the beginning of a very interesting problem with a dead end at the end.
The DateTime type was the default .NET type for working with date and time some time ago, and the logic usually built around it was made as if it would never change. If you try to change the type in one step, it will lead to cascading changes in almost all parts of the system. In extreme cases, you may need to change about 200 stored procedures for only one field. This is the first problem. And the second problem is that the consequences of such changes are difficult to find during testing. Regression testing does not guarantee that you will not miss anything or that the system will function in all cases. The necessary quality assurance efforts will increase as you work, and you will not have a clear understanding of when it will end.
During my research, I found a possible approach to such transformations. This approach has three stages and is based on the assumption that currently the system does not support time zones, and all subsystems are located in the same time zone.
This approach will help localize changes and limit QA efforts. It will also provide good predictability for evaluating future work. Below, I described the steps in more detail.
')
Imagine that there are about 150 fields associated with date / time values. You can use the following SQL script to find out the full list in your database.
While in DB the conversion from DateTime to DateTimeOffset and back is maintained at a very good level, in C # code it is difficult due to typing. You cannot read the DateTime value if the database returns a DateTimeOffset value. When changing the return type for one field, you need to change all the places where it is used throughout the system. In some cases, this is simply not possible, because you may not know about some places if the system is very large. It is for this reason that the approach of simply changing the field type will not work. You can try to find all the uses of a specific table field in the database using the following script.
In order to make the transformation, it is important to predict in advance which parts of the system will be affected. You should have an approach to localizing changes in a specific module of the system without disrupting the remaining parts.
This approach is simply “better” and not the best. I still expect that some problems may appear in the future, but it looks more secure than the previous one. The main difference is that you do not perform the conversion in one step. There is a sequence of dependent changes that will give you control over the situation.
When you add a calculated duplicate field in the database, you enter a new field with the required type. This will allow you to separate the read, write and separate the updated code from the old one. This operation can easily be performed using a script, and no effort to ensure quality is required.
Based on the result above, you can chop your system into sections where you want to enter a DateTimeOffset. Now you can use a new type in only one stored procedure without having to change all the places associated with it.
Read operations were the most difficult to convert because of the approach used to integrate between client code and the database. Date / time values are transmitted by string serialization. DateTimeOffset has a different format and cannot be read by default for client-side DateTime variables. At the same time, write operations just work. If you pass a DateTime value to an argument or a DateTimeOffset field, this value will be accepted with the assumption that it is adjusted to UTC. The time offset after the conversion will be "+00: 00".
Now you can take some section of the system and determine the exact number of storages that return DateTime to the client code. Here it will be necessary to change the read operations in the C # code to read the values of DateTimeOffset. You will also need to change the database itself so that they return values from the new calculated fields. The expected result of this step is as follows:
As a result, you will receive a system that reads data from new fields, while storing values in the old ones. Now, as soon as the time offset is transmitted in the write operations and stored in the system, the entire system will begin to work correctly with time zones.
Now you need to fix the time offset in the system, send it to the database and save it in the fields. It is necessary to take the old field and change it to the one calculated from the new, and the new one should now contain values. You already read them, now write down the values, and the old ones, on the contrary, only read. This approach will help you isolate changes for a specific section only. The expected result is as follows:
As a result, you get a system that writes and reads a new type of DateTimeOffset. This type has built-in support for time offset, so you will not need to do any manual conversion to UTC or between time zones, in general.
The only recommendation I can give regarding the division of the system into sections for the conversion is the following: it is necessary to provide sufficient isolation of the modules in accordance with the used storage. Thus, you will achieve predictability of efforts and be able to evaluate them in advance. Sure, some problems may still arise, but they will not grow like a snowball as you work. Later you can get rid of the old fields. Information about the time zone can be taken from the operating system or user settings. Below I posted information about the compatibility of the two types in the database.
It is very interesting to hear stories about a similar transformation from those who have already done this in their systems. And also who and how supports timezone in their systems.
The DateTime type was the default .NET type for working with date and time some time ago, and the logic usually built around it was made as if it would never change. If you try to change the type in one step, it will lead to cascading changes in almost all parts of the system. In extreme cases, you may need to change about 200 stored procedures for only one field. This is the first problem. And the second problem is that the consequences of such changes are difficult to find during testing. Regression testing does not guarantee that you will not miss anything or that the system will function in all cases. The necessary quality assurance efforts will increase as you work, and you will not have a clear understanding of when it will end.
During my research, I found a possible approach to such transformations. This approach has three stages and is based on the assumption that currently the system does not support time zones, and all subsystems are located in the same time zone.
- Add a pair of calculated field to read the DateTimeOffset values from the database.
- Make the conversion of read operations.
- Make the conversion of write operations.
This approach will help localize changes and limit QA efforts. It will also provide good predictability for evaluating future work. Below, I described the steps in more detail.
')
Failed Approach
Imagine that there are about 150 fields associated with date / time values. You can use the following SQL script to find out the full list in your database.
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 While in DB the conversion from DateTime to DateTimeOffset and back is maintained at a very good level, in C # code it is difficult due to typing. You cannot read the DateTime value if the database returns a DateTimeOffset value. When changing the return type for one field, you need to change all the places where it is used throughout the system. In some cases, this is simply not possible, because you may not know about some places if the system is very large. It is for this reason that the approach of simply changing the field type will not work. You can try to find all the uses of a specific table field in the database 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 make the transformation, it is important to predict in advance which parts of the system will be affected. You should have an approach to localizing changes in a specific module of the system without disrupting the remaining parts.
Better approach
This approach is simply “better” and not the best. I still expect that some problems may appear in the future, but it looks more secure than the previous one. The main difference is that you do not perform the conversion in one step. There is a sequence of dependent changes that will give you control over the situation.
Creating a Computed Field
When you add a calculated duplicate field in the database, you enter a new field with the required type. This will allow you to separate the read, write and separate the updated code from the old one. This operation can easily be performed using a script, and no effort to ensure quality is 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 result above, you can chop your system into sections where you want to enter a DateTimeOffset. Now you can use a new type in only one stored procedure without having to change all the places associated with it.
Reading Transformation
Read operations were the most difficult to convert because of the approach used to integrate between client code and the database. Date / time values are transmitted by string serialization. DateTimeOffset has a different format and cannot be read by default for client-side DateTime variables. At the same time, write operations just work. If you pass a DateTime value to an argument or a DateTimeOffset field, this value will be accepted with the assumption that it is adjusted to UTC. The time offset after the conversion will be "+00: 00".
Now you can take some section of the system and determine the exact number of storages that return DateTime to the client code. Here it will be necessary to change the read operations in the C # code to read the values of DateTimeOffset. You will also need to change the database itself so that they return values from the new calculated fields. The expected result of this step is as follows:
- C # code reads the DateTimeOffset and uses this type wherever possible to return values from the system.
- Database presets use DateTimeOffset in arguments, and C # code passes them the value of DateTimeOffset.
- New type is used inside the database.
- Database strings return values from newly added fields.
As a result, you will receive a system that reads data from new fields, while storing values in the old ones. Now, as soon as the time offset is transmitted in the write operations and stored in the system, the entire system will begin to work correctly with time zones.
Transformation Record
Now you need to fix the time offset in the system, send it to the database and save it in the fields. It is necessary to take the old field and change it to the one calculated from the new, and the new one should now contain values. You already read them, now write down the values, and the old ones, on the contrary, only read. This approach will help you isolate changes for a specific section only. The expected result is as follows:
- C # code creates DateTimeOffset values and transfers them to the database.
- New fields are now real fields with values.
- Old fields are now calculated and used for reading.
- Database preserves save values to new fields.
As a result, you get a system that writes and reads a new type of DateTimeOffset. This type has built-in support for time offset, so you will not need to do any manual conversion to UTC or between time zones, in general.
Further Steps
The only recommendation I can give regarding the division of the system into sections for the conversion is the following: it is necessary to provide sufficient isolation of the modules in accordance with the used storage. Thus, you will achieve predictability of efforts and be able to evaluate them in advance. Sure, some problems may still arise, but they will not grow like a snowball as you work. Later you can get rid of the old fields. Information about the time zone can be taken from the operating system or user settings. Below I posted information about the compatibility of the two types in the database.
- Changing the column type from DateTime to DateTimeOffset works with implicit conversion. The time offset will be +00: 00. If you need to specify a different time zone, you must use a temporary column.
- Formatting values into a string is supported.
- Comparison by all operators supported.
- SYSDATETIMEOFFSET () can replace GETDATE () without risk
- Any assignment between DateTime and DateTimeOffset works with an implicit conversion.
| Operation | T-sql | Comment |
|---|---|---|
| Convert DateTime to DateTimeOffset | TODATETIMEOFFSET (datetime_field, '+00: 00') | Get value with added offset +00: 00 |
| Convert DateTimeOffset to DateTime | CONVERT (DATETIME, datetimeoffset_field) - or - SET @datetime = @datetimeoffset | The offset information will be lost. When converting, the offset will simply be ignored. For example, for '2017-04-05 10:02:00 +01: 00' get '2017-04-05 10:02:00'. |
| Current date / time | SWITCHOFFSET (SYSDATETIMEOFFSET (), '+00: 00') | These are two teams. The result will be a point in the UTC zone. |
| Embedded operations | DATEPART, DATEDIFF, BETWEEN, <,>, =, etc. | DATEDIFF, BETWEEN and comparison operations take into account the time offset, while the DateTime value is represented as a value with a UTC offset |
| Formatting | CONVERT (NVARCHAR, datetimeoffset_field, 103) | Get the same result as for datetime. |
It is very interesting to hear stories about a similar transformation from those who have already done this in their systems. And also who and how supports timezone in their systems.
Source: https://habr.com/ru/post/323608/
All Articles