More about Introduction of Time zones in long-lived Project

Some time ago, we started to adapt the system to the new market that requires support for time zones. Initial research was described in the previous article. Now the approach has slightly evolved under the influence of realities. This article describes the problems encountered during the discussions and the final decision that is implemented.

TL;DR

• It is necessary to distinguish terms:
  • UTC is the local time in the +00:00 zone, without the DST effect
  • DateTimeOffset – local time offset from UTC ± NN:NN, where the offset is the base offset from UTC without the DST effect (in C# TimeZoneInfo.BaseUtcOffset)
  • DateTime – local time without information about the time zone (we ignore the Kind attribute)
• Split up the use into external and internal:
  • Input and output data via API, messages, file exports/imports must be strictly in UTC (DateTime type)
  • Inside the system, the data is stored along with the offset (DateTimeOffset type)
• Split up the use in the old code into non-DB code (C#, JS) and DB:
  • Non-DB code operates only with local values (DateTime type)
  • The database works with local values + offset (DateTimeOffset type)
• New projects (components) use DateTimeOffset.
• In a database, the DateTime type simply changes to DateTimeOffset:
  • In table field types
  • In the parameters of stored procedures
  • Incompatible constructions are fixed in the code
  • Offset information is attached to a received value (simple concatenation)
  • Before returning to the non-DB code, the value is converted to local
• No changes to non-DB code
• DST is solved using CLR Stored Procedures (for the SQL Server 2016 you can use AT TIME ZONE).

Now, in more detail about the difficulties that were overcome.

“Deep-rooted” standards of IT industry

It took quite a lot of time to relieve people from fear of storing dates in local time with offset. Some time ago, if you ask an experienced programmer: “How to support time zones?” – the only option was: “Use UTC and convert to local time just before demonstration”. The fact that for normal workflow you still need additional information, such as the offset and time zone names, was hidden under the hood of implementation. With the advent of DateTimeOffset, such details came out, but the inertia of the “programming experience” does not allow to quickly agree with another fact: “Storing a local date with a basic UTC offset” is the same as storing UTC. Another advantage of using DateTimeOffset everywhere allows you to delegate control over observance of .NET Framework and SQL Server time zones, leaving for human control only the moments of data input and output from the system. Human control is the code written by a programmer to work with date/time values.

To overcome this fear, I had to hold more than one session with explanations, presenting examples and Proof of Concept. The simpler and closer the examples to those tasks that are solved in the project, the better. If you start out in the discussion “in general”, this leads to a complication of understanding and wasting time. Briefly: less theory – more practice. The arguments for UTC and against DateTimeOffset can be related to two categories:

• “UTC all the time” is the standard and the rest does not work
• UTC solves the problem with DST

It should be noted that neither UTC nor DateTimeOffset solves the problem with DST without using information about the rules for converting between zones, which is available through the TimeZoneInfo class in C#.

Simplified model

As I noted above, in the old code, changes happen only in a database. This can be assessed using a simple example.

Example of a model in T-SQL

// 1) data storage
// input data in the user's locale, as he sees them
declare @input_user1 datetime = '2017-10-27 10:00:00'

// there is information about the zone in the user configuration
declare @timezoneOffset_user1 varchar(10) = '+03:00'
 
declare @storedValue datetimeoffset

// upon receiving values, attach the user’s offset
set @storedValue = TODATETIMEOFFSET(@input_user1, @timezoneOffset_user1)

// this value will be saved
select @storedValue 'stored'
 
// 2) display of information
// a different time zone is specified in the second user’s configuration,
declare @timezoneOffset_user2 varchar(10) = '-05:00'

// before returning to the client code, values are reduced to local ones
// this is how the data will look like in the database and on users’ displays
select
@storedValue 'stored value',
CONVERT(DATETIME, SWITCHOFFSET(@storedValue, @timezoneOffset_user1)) 'user1 Moscow',
CONVERT(DATETIME, SWITCHOFFSET(@storedValue, @timezoneOffset_user2)) 'user2 NY'
 
// 3) now the second user saves the data
declare @input_user2 datetime

// input local values are received, as the user sees them in New York
set @input_user2 = '2017-10-27 02:00:00.000'

// link to the offset information
set @storedValue = TODATETIMEOFFSET(@input_user2, @timezoneOffset_user2)
select @storedValue 'stored'
 
// 4) display of information
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 execution will be as follows.

The example shows that this model allows making changes only in the database, which significantly reduces the risk of defects.

Examples of functions for processing date/time values

// When receiving values from the non-DB code in DateTimeOffset, they will be local, 
// but with offset +00:00, so you must attach a user’s offset, but you cannot convert between 
// time zones. To do this, we translate the value into DateTime and then back with the indication of the offset 
// DateTime is converted to DateTimeOffset without problems, 
// so you do not need to change the call of the stored procedures in the client code

create function fn_ConcatinateWithTimeOffset(@dto datetimeoffset, @userId int)
returns DateTimeOffset as begin
    declare @user_time_zone varchar(10)
    set @user_time_zone = '-05:00' // from the user's settings @userId
    return todatetimeoffset(convert(datetime, @dto), @user_time_zone)
end

// Client code cannot read DateTimeOffset into variables of the DateTime type, 
// so you need to not only convert to a correct time zone but also reduce to DateTime, 
// otherwise, there will be an error

create function fn_GetUserDateTime(@dto datetimeoffset, @userId int)
returns DateTime as begin
    declare @user_time_zone varchar(10)
    set @user_time_zone = '-05:00' // from the user's settings @userId
    return convert(datetime, switchoffset(@dto, @user_time_zone))
end

Small Artifacts

During the adjustment of SQL code, some things were found that work for DateTime, but are incompatible with DateTimeOffset:

GETDATE()+1 must be replaced with DATEADD (day, 1, SYSDATETIMEOFFSET ())

The DEFAULT keyword is incompatible with DateTimeOffset, you need to use SYSDATETIMEOFFSET()

The ISNULL(date_field, NULL)> 0″ construct works with DateTime, but 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 deal with the conversion when receiving and returning data. Then why do we need all this, if there is a well-tried 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.
• Conversion can be minimized if DateTimeOffset is used everywhere, performing it only before displaying data or outputting it to external systems.

These reasons seemed to me essential owing to using this approach.

I will be glad to answer your questions, please write comments.