Skip to content
Advertisement

DateTime in UTC within the database and perform no timezone conversion from the controller, handle conversions only in the client browser

I was faced with a problem which there are many discussions about on SO. I actually read SO questions & answers for 2 days trying to determine a good way to handle my problem. The problem is a fairly basic concept. Dates and Times should be sent to the mvc controller method and saved in the database as UTC. All of the answers that I could find about the topic were about handling the UTC conversion in the controller method itself. I wanted specifically to handle all date conversions in the client code. I also had a need to use SQLCommand for my query on the table that held the dates rather than using Linq/EF. Some of the solutions I found we’re about using DateTime.SpecifyKind and using JsonSerializerSettings as DateTimeZoneHandling.Utc in Newtonsoft Serialization.

For some examples… I have my date stored in the Sql Server database as a DateTime nullable datatype. I use military time and store it in the following format…

2020-05-04 16:52:00.000

The following code is all client code that is written in html/jquery/js:

In the system I am working on, I use flatpickr to set up a date chooser on an input.

JavaScript

Then I create the flatpickr instance with Date and Time…

JavaScript

flatpickr_for_add is now an instance that can be called with methods. The next thing I do is I get the flatpickr data to a string like this:

var strInputDate = flatpickr_for_add.selectedDates[0];

My question is, how do I convert this date and time from the flatpickr string to UTC and then store it in the database using any/all of AJAX/JSON/JS/JQUERY/C# MVC SqlServer?

The second part of my question is, how do I retrieve the date time from the database and return it to the browser while converting the date time from UTC to the user’s local browser timezone?

Advertisement

Answer

Here is the way that I ended up handling this. I hope that it helps someone else and I also hope that I am not putting any questionable techniques out there by putting this post together.

In my c# mvc server code my class looks similar to this…

JavaScript

Notice that I use DateTime? for the type, not DateTimeOffset?

Assuming you have your SQLCommand formatted and ready to go and your class (in my case GridRecord is the name of the class) matches the result of the sql column data, do this in your c# mvc controller method:

JavaScript

The code above is really a pretty simple concept. We grab the result from a SQLCommand, store it in a datatable and then use Newtonsoft Json.NET to serialize/deserialize it to a list typed to the class (my class is GridRecord).

The next thing that I did is to set up the controller method to return a result. I used ActionResult instead of JsonResult, because JsonResult actually alters the datetime during serialization. In my case the controller method is called GetGridData.

JavaScript

Notice that we must use the JsonSerializerSettings to define DateTimeZoneHandling as Unspecified. This is the reason to use ActionResult rather than using JsonResult.

For the client code we have a lot more flexibility on the format that we pass to the controller method. I do suggest that you use military time when you store the data in the database. I am not going to get too detailed with the ajax call here to send the date to the server and save it in the database because it is up to you if you want to do a GET or POST to call the method, and it is also up to you how you want to set up your AJAX dataobject.

Here is an example of what I did for that piece for the controller method (notice it’s okay to use JsonResult here because we are just posting data with no real expectation of a return, except maybe an error code). You might do something like this:

JavaScript

Notice that in the code above I receive the parameter to the controller method as a string, and then use Convert.ToDateTime() to handle the date string conversion for the DateTime datatype in the database.

To format the date on the end user’s browser to UTC before sending it to the controller method, in the javascript/jquery code I use moment.js:

JavaScript

Or you can send the current date and time from the user’s browser by using:

JavaScript

The format is very flexible with this solution if you would like to go with something other than MM/DD/YYYY HH:mm as long as the format you use is moment.js friendly. Like I said, you may want to stick with military time so you don’t have to deal with AM/PM. Check the moment.js documentation for further information on acceptable formatting.

Once you receive the data back from the controller method and you are ready to convert the date/time from UTC back to the user’s browser date time, then you use a moment.js statement like this…

JavaScript

UPDATE After using the code mentioned above to convert the utc time to the local timezone for a while, I noticed that this line of code to convert to local time is not always working. I am now using a function combined with moment-timezone for the part that converts the UTC time retrieved from the controller method, to the user’s local timezone

  1. added moment timezone latest stable npm i moment-timezone
  2. added moment-timezone-with-data-2012-2022.min.js to my bundle
  3. added a function to my js code which I found here by Rohit Parte
JavaScript
  1. Replaced the line in my js code to convert from utc to local time

Replaced

JavaScript

With

JavaScript
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement