More about the introduction of timezone in the long-lived project
Some time ago, we began to adapt the system to the requirements of the new market, where support for timezone is required. Initial research described in the previous article . Now the approach has evolved a little under the influence of reality. Under the cut description of the problems encountered during the discussions, and the final decision that comes to life.
Now more about the overcome difficulties.
It took quite a long time to save people from fear of storing dates in local time with offset. Some time ago, if you ask a programmer with experience: "How to support time zones?" - the only option was: "Use UTC, and convert to local time just before the show." The fact that additional information, such as the offset and the names of the timezone, is still needed for normal operation, was hidden under the realization hood. With the advent of DateTimeOffset, such details got out, but the inertia of the “programming experience” does not allow one to quickly agree with another fact: “Storing a local date with a base offset from UTC” is the same as UTC storage. Another advantage of using DateTimeOffset everywhere allows you to delegate control over compliance with the time zone. NET Framework and SQL Server, leaving for human control only the moments of data input and output from the system. Under human control, I mean code written by a programmer for working with date / time values.
To overcome this fear, I had to spend more than one session with explanations, presenting examples and Proof Of Concept. The simpler and closer the examples to the tasks that are solved in the project, the better. If you go into the argument "in general", then this leads to a complication of understanding and wasting time. In short: less theory - more practice. Arguments for UTC and against DateTimeOffset can be attributed to two categories:
')
It should be noted that neither UTC nor DateTimeOffset solves the problem with DST without using the information about the rules for converting between zones, which is available through the TimeZoneInfo class in C #.
As noted above, in the old code changes occur only in the database. How exactly this works can be estimated using a simple example.
The result of the script will be as follows.
By the example, it is clear that this model allows you to make changes only in the database, which significantly reduces the risk of defects.
During the adaptation of the SQL code, some things were discovered that work for DateTime, but are incompatible with DateTimeOffset:
Someone may notice that, as in the approach with UTC, we are engaged in conversion when receiving and returning data. Then why is this all, if there is a proven and working solution? There are several reasons for this:
These reasons seemed to us essential for using the described approach. I will be glad to answer questions, write in comments.
TL; DR
- It is necessary to distinguish between the terms:
- UTC - local time in the zone +00: 00, without the effect of DST
- DateTimeOffset - local time offset from UTC ± NN: NN, where the offset is the base offset from UTC without the effect of DST (in C # TimeZoneInfo.BaseUtcOffset)
- DateTime - local time without timezone information (we ignore the Kind feature)
- Separation of use into external and internal:
- Incoming and outgoing data via API, messages, file export / import must be strictly in UTC (type DateTime)
- Inside the system, data is stored with the offset (type DateTimeOffset)
- Separation of use in the old code into non-DB code (C #, JS) and DB:
- non-DB code operates only with local values ​​(type DateTime)
- The database works with local values ​​+ offset (type DateTimeOffset)
- New projects (components) use DateTimeOffset.
- In the database, the DateTime type simply changes to DateTimeOffset:
- in table field types
- in the parameters store
- incompatible constructs are fixed in the code
- bind information is added to the received value (simple concatenation)
- before returning to a non-db code, the value is reduced to a local
- No change in non-db code
- DST is solved using CLR Stored Procedures (for SQL Server 2016, you can use AT TIME ZONE ).
Now more about the overcome difficulties.
"Sewn" standard IT industry
It took quite a long time to save people from fear of storing dates in local time with offset. Some time ago, if you ask a programmer with experience: "How to support time zones?" - the only option was: "Use UTC, and convert to local time just before the show." The fact that additional information, such as the offset and the names of the timezone, is still needed for normal operation, was hidden under the realization hood. With the advent of DateTimeOffset, such details got out, but the inertia of the “programming experience” does not allow one to quickly agree with another fact: “Storing a local date with a base offset from UTC” is the same as UTC storage. Another advantage of using DateTimeOffset everywhere allows you to delegate control over compliance with the time zone. NET Framework and SQL Server, leaving for human control only the moments of data input and output from the system. Under human control, I mean code written by a programmer for working with date / time values.
To overcome this fear, I had to spend more than one session with explanations, presenting examples and Proof Of Concept. The simpler and closer the examples to the tasks that are solved in the project, the better. If you go into the argument "in general", then this leads to a complication of understanding and wasting time. In short: less theory - more practice. Arguments for UTC and against DateTimeOffset can be attributed to two categories:
')
- "UTC all the time" is standard and the rest is not working
- UTC solves a problem with DST
It should be noted that neither UTC nor DateTimeOffset solves the problem with DST without using the information about the rules for converting between zones, which is available through the TimeZoneInfo class in C #.
Simplified Model
As noted above, in the old code changes occur only in the database. How exactly this works can be estimated using a simple example.
An example of a model in 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' The result of the script will be as follows.
By the example, it is clear that this model allows you to make changes only in the database, which significantly reduces the risk of defects.
Examples of functions for handling date / time values
-- - 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 Small Artifacts
During the adaptation of the SQL code, some things were discovered that work for DateTime, but are incompatible with DateTimeOffset:
- GETDATE () + 1 must be replaced by DATEADD (day, 1, SYSDATETIMEOFFSET ())
- DEFAULT keyword is incompatible with DateTimeOffset, you must use SYSDATETIMEOFFSET ()
- the construction ISNULL (date_field, NULL)> 0 "works with DateTime, but for DateTimeOffset should be replaced with" date_field IS NOT NULL "
Conclusion or UTC vs DateTimeOffset
Someone may notice that, as in the approach with UTC, we are engaged in conversion when receiving and returning data. Then why is this all, if there is a proven and working solution? There are several reasons for this:
- DateTimeOffset allows you to forget where SQL Server is located.
- This allows you to shift part of the work to the system.
- Conversions can be minimized if DateTimeOffset is used everywhere, making them only before displaying data or outputting them to external systems.
These reasons seemed to us essential for using the described approach. I will be glad to answer questions, write in comments.
Source: https://habr.com/ru/post/325410/
All Articles