Orchardcore: NodaTime : Standardize timezones for x-plat

[Skrypt]

Right now the issue is that we are using Windows TimeZones when using OC under Windows but when moving the website to a Linux/Mac system they are not having the same timezones. Different problems comes from TimeZoneInfo.GetSystemTimeZones() which could be fixed by using NodaTime.

The drawbacks of migrating mainly comes from our usage of DateTime type within Orchard everywhere. We either will need to replace all of those with NodaTime LocalTime or provide some Helper functions to convert from one to the other. Same goes with TimeZones, we need to be able to also have a helper function that will return NodaTime TimeZones to Windows TimeZones ... etc ...

@joeaudette solution
https://www.cloudscribe.com/docs/easy-timezone-handling

@andrewhanson solution
https://hanson.io/taming-the-datetime-beast-with-noda-time/
https://github.com/andrewhanson/NodaTimeExplorer

[juanp123]

As background on the issue:

Windows uses its own timezone implementation at the OS/registery level. Windows time-zones are not globally complete (some users need to select wrong time-zones to get their time handled) and only provide historic data back to roughly 1970.

The Olson timezone DB (https://www.iana.org/time-zones] is used by practically everything that is not windows, including anything Apple, Android, and *nix. The Olson DB has more global coverage than window's time-zones. It also has the most complete historic timezone information, dating back (I believe) as far as time-zones have been registered. Olson is also used in standards like iCal and CalDAV and is pretty much the basis for every calendar system out there (Google, Apple, Mozilla).

It has been proposed that window's timezone can be mapped to Olson Ids when needed. Total mapping however is not possible. Window's time zones are an incomplete subset of Olson's (http://cldr.unicode.org/development/development-process/design-proposals/extended-windows-olson-zid-mapping). The mapping in either direction would result in multiple candidates in some cases. An example issue using orchard and .net core is shown here (https://github.com/dotnet/corefxlab/issues/338) . In a few fringe cases, Olson IDs do not map to windows. The windows registry format cannot support all rules that are in the Olson database.

A proposed solution is to include Nodatime as the basis for Orchard's timezones and datetime handling. Nodatime is based on the Olson database.

Pluses for Olson + Nodatime

• Adopting the Olson DB would allow Orchard to be compatible when communicating with the outside world
• Olson would provide the best (most compatible and accurate) out-of-the-box support for community modules that are timezone based. Anyone writing a serious calendar or scheduling system will likely use nodatime anyhow.
• Timezone and daylight savings time calculation (for Orchard itself and community modules) would be less error prone. Nodatime provides a more complete and sensible API for handling timezone conversions and handling daylight savings time. (https://nodatime.org/2.2.x/userguide/rationale)

Minuses for Olson + Nodatime

• Would create a hard dependency on Nodatime.
• Nodatime creates a new release when the Olson database changes. Getting the new data into orchard 'auto-magically' may not be feasible (https://nodatime.org/2.2.x/userguide/tzdb)
• The Olson DB is released whenever there are timezone changes, sometimes on short notice. There appears to be some lag (versus OS level updates) in getting that back to users such as nodatime. (https://github.com/nodatime/nodatime/issues/473)
• Performance?

Pluses for using Windows IDs

• It is a familiar timezone for window's users. Questionably may be needed for inter-op with other MS systems (?)
• Updated at the OS level (if hosted on windows)

Minuses for Windows Time-Zones

• Incomplete timezone coverage.
• Less historic data on timezone changes.
• Cannot support all timezone rules.
• Harder API to use correctly (i.e DateTime and TimeZoneInfo) (https://blog.nodatime.org/2011/08/what-wrong-with-datetime-anyway.html) ( i.e. even MS people apparently get it wrong .. https://codeofmatt.com/2015/04/20/beware-the-edge-cases-of-time/ )

If window's time-zones are used as Orchard's standard timezone, inevitably someone will contribute an Olson DB alternative. This will lead to having two standards and incompatibility between modules.
It is preferable that Orchard uses one standard as the global source of truth for itself and the user community.

Given Orchard is not yet released. Perhaps a 'sit back and wait' ( as long as possible) approach would be best to see if .net core solves the issue first. They may create a new timezone api or other solution that hopefully would be compatible with Orchard.
(https://github.com/dotnet/corefxlab/issues/338)
(https://github.com/dotnet/corefx/issues/11897)

[Skrypt]

Here is also one library that could be usefull for only converting TimeZones.

https://github.com/mj1856/TimeZoneConverter

Nodatime videos :

https://vimeo.com/77323770
https://www.youtube.com/watch?v=saeKBuPewcU

[juanp123]

There is no lib that can convert between time-zones and always be accurate. It is not possible. Note the author says "The author's best-informed knowledge and opinions".

[Skrypt]

I think that the library I linked is actually the implementation in .NET Core 2.0. So, yes you are right about that.

I also think that we should really replace System.Datetime entirely ; else we are missing the point about why we would be using NodaTime. NodaTime is also about approaching coding DateTimes differently.

[BennieCopeland]

I don't know if this is related to the topic, but I get the following while trying to change the timezone in Beta1 on Ubuntu. No specific order that I can tell, and tons of duplicate entries.

[sebastienros]

I am fine using NodaTime to handle timezones, but in the UI and the database we still need to use DateTime. The difference is that in the database values will always be converted to Utc and for the UI they will be provided as Local.

[sebastienros]

Other options
1- to use DateTime only for database
2- use a long for database and get rid totally of it.

[Skrypt]

PostgreSQL supports NodaTime types ; here are the mappings :

https://github.com/npgsql/npgsql/issues/546

For SQL Server :

https://stackoverflow.com/questions/30035072/how-should-i-persist-timestamps-in-sql-db-if-app-uses-nodatime

SQLite should be the same.

[ArtKarp]

"The difference is that in the database values will always be converted to UTC and for the UI they will be provided as Local."

I hope that this is only one option.

Orchard needs a NON UTC date/time field. (if it doesn't already have one) with the option of including a time zone.

There are many date/times that need to be saved to a database and never converted when displayed in the UI. They also need a way to store a specific time zone. When the date/time is used in reporting it won't require any conversion and will always be accurate. Without this ability, the UI will incorrect. If you try converting a date of birth for UI someone viewing the record in California sees March 2 and someone sees it in Sydney as March 3. The both users need to see the true and accurate date/time not a converted one.

Examples:
An insurance policy expires on March 2, 2019 12:01 AM Pacific Time (no conversion - everyone needs to be clear on the expire date/time/time zone)

A date of birth is March 2, 2019 6:15 PM (time zone optional, not required)

A music concert scheduled on March 2, 2019 7 PM (local time based on the city where the concert is held - no time zone required)

I bring this up because Microsoft Dynamics CRM didn't start out including a non UTC date/time field. It took years to get it added/fixed. Users had to create workarounds and deal with inaccurate date/times.

Please do this correctly now.
Thanks

[sebastienros]

@ArtKarp We already have an issue in O1 (that we take into account in O2) to have such fields without timezone. Thanks for the reminder, we'll definitely have you review it when it's done.

[jpiquot]

Hi,
Instead of storing an UTC DateTime. Can't we use a DateTimeOffset that stores the local time zone?

Regards,

[Skrypt]

Date of birth can still be confusing since the person could be born in a different timezone than where she actually lives. I understand that this is optional relatively depending on how this information will be used afterward.

Let's not make a mistake on how the dates should be stored and how they should be output "differently" in the app depending on the context. Using DateTime.UtcNow within the app on runtime should not be a big issue if we already know which current timezone the user session has. Though when needing to retreive back this information from the database I'm pretty sure that we should save timezones as part of the datetime since a user can also change from a timezone to an other and also timezones themselves can change at any time.

[juanp123]

I left a comment here last night that apparently didn't get posted.

Along the lines of what @Skrypt is saying above, it would be a nice to have a 'timezone' property added to the built in Orchard user. That would allow for module interop. In Orchard 1.x such a property was added by extending the user (typically a profile module) and while that was workable, it also meant there was no common place to find the setting and risked modules creating incompatible or conflicting duplicate settings. If any module wants to localize datetime per user, it would be very helpful to have a centralized (i.e. built in) place where one can lookup the user's preferred timezone.

A use case may be a module that sends a 'happy new years' email at the stroke of midnight (i.e. a workflow trigger that uses timezones per user ). There could be a calendar that localizes to the user's specified timezone (ie office time instead the timezone where they are). Without a centralized setting each module has to create its own. I realize a lot of people localize pages based on the local computer's timezone but that does not work for running logic server side.

I should stress that a user's timezone property would be defined but optional to use. There would be no need to set it if one didn't want to use it.

Alternatively, since I know keeping 'core' barebones is somewhat of a religion, perhaps just a published list of 'Recommended Default User Properties Names for Module Interoperability' would work. Things like 'timezone', 'lastLogin' would be candidates.

[ArtKarp]

In Dynamics CRM I built a cross reference table that I have included in an attached file.
Reasons for doing this were to address...

Data Entry Users Need:

-To select a time zone they understand when populating a date/time field (e.g. "Pacific Time |Los Angeles")

Users and Reports Need:

-An abbreviation of the time zone (e.g. "PT")
-To show the full name of the time zone (e.g. "Pacific Time")
-Might also include the +/- GMT info (e.g. "1-Jan-2017 11:00 AM (-8:00) GMT)

(Note: I use the text "Pacific Time" rather than "Pacific Standard Time" or "Pacific Daylight Time" because the "Standard" and "Daylight usually just adds unnecessary information. If you have to be at an event at 4:00 PM Pacific time, it doesn't matter if it is Standard or Summer time on that date.)

Developers Need:

May need to know the Windows Time Zone Number for IIS and .NET (e.g. "004" for Pacific Standard Time")
May need to know the IANA Zone Name for cross platform OS and development languages (e.g. "America/Los_Angeles", "Europe/Paris")
The offset hours from GMT for Standard Time and Daylight (Summer) time
Ability to import/use datetime data from external systems that do not include a time zone
To not recalc data that isn't being edited in order to display it

System Needs:

• To enable only valid usable time zones for the organization/company. If you are an Australian website and all your activity is scheduled in country or in one state, you do not need/want to see a timezone list that includes the rest of the world. However, if you are a company that works across Europe, you might want to include all European time zones when users enter a date/time.

• A system users default time zone. This is helpful for data entry as a starting point. Also, helpful to know when the person is most likely awake. :)

Other Notes:

Data fields to cross reference the IDs from TimeAndDate.com and WorldTimeServer.com were included because they offer commercial APIs that can be used to find out information about a specific date/time/timezone. (e.g. "Is March 15, 2018 4:00 PM in Los Angeles standard time?", "Is a specified date a bank holiday", "If it is July 4th 8:00 AM in Los Angeles, what date/time is it in New York, London and Sydney")

Keep in mind that the GMT Offset and daylight/summer time and its offset minutes is set by each country/region by law. Because of this, there are currently 27 standard time zones. Even within a country like the USA, the state of Arizona has its own "Mountain" time zone which different from the Mountain time zone of Denver. Arizona doesn't observer Daylight changes. It is on Standard time all year. The same thing happens in the state of Queensland Australia - no DST.

You can read all about it here:
https://www.timeanddate.com/time/current-number-time-zones.html

All Time Zones.xlsx

[Skrypt]

Here is also the "complete" list of IANA timezones.
https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

I understand that Microsoft Dynamics installs only on Windows so the cross reference table has been built to keep in sync with other systems but starts from Windows TimeZones.

If we can install Orchard on different OS then the timezones differs from one to the other unless we decide to opt in with something that will give us the same adequate list of TimeZones for every OS. NodaTime gives us the ability to have that same TimeZone list for every OS. Right now .NET Core doesn't retrieve the TimeZones adequately as we reported before. On Mac OS the list of TimeZones is displaying only "GMT -X" without any description. The list of timezones is also longer on Mac and Linux than on Windows because of how .NET Core has implemented this so far. Our issue stands also with deployment scenario's where someone could develop on Windows and deploy on a Linux server.

Seems to me, after reading last post, that if we would use IANA timezones by default then we would be needing a cross reference table from IANA to other timezone systems (Windows, commercial APIs). Though Orchard itself doesn't require to have this unless it would need to consume some other commercial API's that use other Timezone data.

Thinking globally ; what would be really helpfull is an open source database of cross references between those different TimeZone systems.

[Skrypt]

In addition to being a superior date time API, the Noda Time library contains an embedded copy of the CLDR mappings. https://nodatime.org/2.1.x/api/NodaTime.TimeZones.Cldr.html

https://stackoverflow.com/questions/17348807/how-to-translate-between-windows-and-iana-time-zones

http://cldr.unicode.org/development/development-process/design-proposals/extended-windows-olson-zid-mapping

https://github.com/mj1856/TimeZoneConverter

I've updated my earlier post because I think this is still relevant. Needs to be tested though.
After looking at the TimeZoneConverter repository I found this :
https://github.com/mj1856/TimeZoneConverter/tree/master/src/TimeZoneConverter/Data
Which is also TimeZone Id's mapping databases from different systems just like @ArtKarp did for Microsoft Dynamics.