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

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

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.

  • codingsight

    Cool!

  • Juozas Alševskis

    Daylight saving time factor missing. Broken date may be loaded to frontend/ wrong date saved depending on situation. No way to load date to frontend using simple functions like {todatetimeoffset/switchoffset}. Need to use user defined fn to handle DST case. I had a lot of headache with this. Our servers are in time zone X, clients zone Y, frontends – worldwide. All frontends supports datetime only.