18 December 2015

Localization vs. Internationalization (i18n) in SQL and .NET

Internationalization is often written i18n, where 18 is the number of letters between i and n in the English word. Internationalization typically entails: Designing and developing in a way that removes barriers to localization or international deployment.

More about the term is described elegantly at w3


There are many issues in handling time in systems that span globally.
One area is the database level and the other is the code level.

Prior to the introduction of the DateTimeOffset type the types offered did not let you save both the UTC time and the actual local timezone in one value. This would typically be handled by composing time upon two stored values, the datetime in UTC and the TimeZoneOffset in another field.

These shortcomings have been handled with the introduction of the DateTimeOffset value in both SQL and .NET. There is still room for improvement because we do not have the Daylight Saving Time Parameter in the type, but this is a value that is set nationally and just a pain to handle ;)

What have the larger systems been doing prior to this?

E.g.The Sitecore platform has saved all internal datetime values to the database as UTC time and let the presentation layer handle the formatting to localized time. For internal values like a publishing pipeline or an event queue having a single point of time reference is critical. This is still a relevant for system values where you want to be precise in the time comparison.

DateTimeOffset (from SQL server 2008)


SQL Server 2008 added a new data type named “datetimeoffset”. This is similar to the old datetime data type, with the following significant differences:
  • Internally, the time is stored in unambiguous UTC format
  • The local time zone offset is stored along with the UTC time, which allows the time to be displayed as a local time value (or converted to any another time zone offset)
  • The data type is capable of storing more precise times than datetime

DateTimeOffset (from .NET Framework 3.5)

The same type has been introduced in .NET v3.5, it can map the new SQL type.

Choosing Between DateTime, DateTimeOffset, TimeSpan, and TimeZoneInfo

The Daylight Saving Time - the missing link... 

I have found one excellent resource for handling Daylight Saving Time. The idea is to let the user select there country of origin, and save the Daylight Offset value at the same time, it can also save the gmt offset if you choose.

The right way is to find the value through the .NET framework with the TimeZone information for a region. But allowing it to be part of a custom value set by the user selection can help give a simplicity around the value. 

No comments: