0

Currently records are being stored in UTC of when the record was actually entered into the system.

My task is to now allow users to enter records of past events and when those events occurred locally to them when the event occurred.

My dilemma is that there is not only different time zones but there is also daylight savings observation during certain periods of the year.

Reporting personal events accurately in charted format are key, so my question is, how do I accurately report the correct date to the user? Because people relocate into different time zones the same person could be reliving the same date and time in a different world locations.

How is the best way to store and display these events to the user? The form data is entered through a asp.net c# web app and being stored in an SQL Server database. The time and date entered will always be the users local date and time.

cloudseeker
  • 245
  • 1
  • 2
  • 13
  • Keep storing them in UTC. You are not specifying how the users are adding those dates and where you need to display them (web app, desktop app, etc.). – Federico Dipuma Sep 23 '17 at 22:30
  • Hi, sorry... these are being displayed in a web app using c# asp.net. – cloudseeker Sep 23 '17 at 22:31
  • My advice is to always use UTC dates for storing and exchanging data. When you need to display them (or acquire a new date) your best option is to rely entirely on client-side technologies (javascript) which will use the current user timezone information for date display and creation. – Federico Dipuma Sep 23 '17 at 22:39
  • Thanks Federico! So would you recommend on insertion of the record converting the date and time inputted by the user to UTC and then doing the conversion back to the user on display of the records? How would you recommend handling international relocation or temporary Timezone change for the user? It means that there would then be an overlap, or their records would display as earlier or later than they really did because they were in a different Timezone during the occurrence but then moved back to their original Timezone. Am I better to store the UTC OFFSET and DST as part of the calculation? – cloudseeker Sep 23 '17 at 22:53
  • Why do you need to store those information? When you display the dates (in a chart you say) you show them in the current user timezone, so there is no overlap at all. UTC dates are used for this purpose, they are unambiguous. When you convert them to the user timezone (which is done automatically by javascript when you construct a date from an ISO string, for instance) they are again unambiguous because they reflect the current timezone information. – Federico Dipuma Sep 23 '17 at 22:58
  • Would that not make a dinner event at 9pm in London, a breakfast event at 6am in Brisbane Australia? – cloudseeker Sep 23 '17 at 23:22
  • This could be immensely complicated, and it is probable that your company policies and employment laws do not adequately address this. Factors to consider are signup deadlines for things like health care plans, expressed in local time, overtime pay and holiday pay based on local time, etc. Also records must be stored in a way that if it's entered as 17:00 local time today, and the record is viewed years later after summer time law change, it's still 17:00. – Gerard Ashton Sep 25 '17 at 10:33
  • Completely agree with you @GerardAshton though the policies and laws are not a concern in this usage as this is personal user supplied data and the events are for their own record. I know that sorting the data is always going to be best from the UTC record as this will prevent overlapping in the time fold, but should this in effect also be storing it as local time and offsets (DST and UTC) to account for movement across the time zones? – cloudseeker Sep 25 '17 at 11:02

1 Answers1

1

For single occurrence events in the past, the easiest solution would be to store using a datetimeoffset field in SQL Server, and use the corresponding DateTimeOffset type in .NET. The date and time portion of the datetimeoffset value reflects the local time of the event, and the offset reflects how far that local time is ahead or behind UTC at that moment. For more, see DateTime vs DateTimeOffset.

This approach doesn't need any additional compensation for DST. As it's only a single occurrence, the offset is inclusive of both the standard time and any applicable daylight saving time in effect at that moment.

The tricky part will be collecting an accurate DateTimeOffset input value from the user of your MVC web site. At the moment, there is not a simple built-in control for a DateTimeOffset. Instead, you will need to present an input box for a DateTime, and separately present a drop-down list of possible offsets. I have some demo code here that will show you how to generate that list. Use it to populate a dropdown, and you can let your user choose the offset. Hopefully this will eventually get merged into ASP.NET Core, so you'll just be able to bind to a DateTimeOffset directly someday. Some discussion on this in aspnet/Mvc#6648.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575