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.

<div class="flatpickrcontainer">
     <input type="text" id="time" class="form-control" placeholder="Select Date..." data-input>
</div>

Then I create the flatpickr instance with Date and Time…

    flatpickr.l10ns.default.firstDayOfWeek = 1; // Monday default is sunday
    const addDateInput = document.querySelector(".flatpickrcontainer");
    flatpickr(addDateInput,
        {
            wrap: true,
            weekNumbers: false,
            defaultDate: new Date(), //set the date to now as the default
            onReady: function () {
                if (this.amPM)
                    this.amPM.textContent = "PM";
            },
            enableTime: true, // enables timepicker default is false    
            time_24hr: false, // set to false for AM PM default is false
            dateFormat: "n/j/yy h:i K"
        });
    flatpickr_for_add = document.querySelector(".flatpickrcontainer")._flatpickr;

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?

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…

public class GridRecord
{
     public int id { get; set; }
     public DateTime? opened_dt { get; set; }
     public DateTime? closed_dt { get; set; }
}

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:

//...here we would set up the connection string and define a variable typed to the class (GridRecord) 
//that will hold the returned data from the sqlcommand... for this variable I will use var sqlGridData 
//... and we can handle any passed in values from the controller method parameters, 
//like specialdata here in this area before we call the sqlcommand...
using (SqlConnection sqlconn = new SqlConnection(constring))
{
     SqlCommand command = new SqlCommand(sql, sqlconn);
     sqlconn.Open();
     SqlDataReader reader = command.ExecuteReader();
     try
     {
          var dataTable = new DataTable();
          dataTable.Load(reader);
          if (dataTable.Rows.Count > 0)
          {
               var serializedMyObjects = JsonConvert.SerializeObject(dataTable);
               sqlGridData = (List<GridModel.GridRecord>)JsonConvert.DeserializeObject(serializedMyObjects,typeof(List<GridModel.GridRecord>));
          }
     }
     //add catch{} here to handle exception if desired (good practice)
     finally
     {
          reader.Close();
     }
}

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.

public ActionResult GetGridData(string specialData = default(string))
{
     //...perform some controller method actions here, like handling specialdata
     //parameter and include the SqlCommand code with proper connectionstring info...
     return Content(JsonConvert.SerializeObject(sqlGridData, new JsonSerializerSettings { DateTimeZoneHandling = DateTimeZoneHandling.Unspecified }));
}

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:

[HttpPost]
public JsonResult Add(string opened_dt = default(string))
{
     result = 0; //you could use this to handle some error like opened_dt = ""
     using (var dbContext = new entities())
     {
          TBL_special rowToAdd = new TBL_special(); //TBL_special is in the database and imported into your edmx model
          rowToAdd = new TBL_special{ opened_dt = Convert.ToDateTime(opened_dt)};
          try
          {
               dbContext.SaveChanges();
          }
          catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
          {
               Exception raise = dbEx;
               foreach (var validationErrors in dbEx.EntityValidationErrors)
               {
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                         string message = string.Format("{0}:{1}",validationErrors.Entry.Entity.ToString(),validationError.ErrorMessage);
                         // raise a new exception nesting
                         // the current instance as InnerException
                         raise = new InvalidOperationException(message, raise);
                    }
                }
                throw raise;
          }

     }
     return Json(result, JsonRequestBehavior.AllowGet);
}

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:

moment.utc(moment(dateStringVariableFromInput).utc().format('YYYY-MM-DD HH:mm:ssZ')).format('MM/DD/YYYY HH:mm');

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

moment.utc(new Date()).format("MM/DD/YYYY HH:mm")

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…

moment.utc(moment(dateStringFromController).format('YYYY-MM-DD HH:mm:SS')).local().format('MM/DD/YYYY h:mm A');

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

function utcToLocal(utcdateTime, tz) {
    var zone = moment.tz(tz).format("Z") // Actual zone value e:g +5:30
    var zoneValue = zone.replace(/[^0-9: ]/g, "") // Zone value without + - chars
    var operator = zone && zone.split("") && zone.split("")[0] === "-" ? "-" : "+" // operator for addition subtraction
    var localDateTime
    var hours = zoneValue.split(":")[0]
    var minutes = zoneValue.split(":")[1]
    if (operator === "-") {
        localDateTime = moment(utcdateTime).subtract(hours, "hours").subtract(minutes, "minutes").format("YYYY-MM-DD HH:mm:ss")
    } else if (operator) {
        localDateTime = moment(utcdateTime).add(hours, "hours").add(minutes, "minutes").format("YYYY-MM-DD HH:mm:ss")
    } else {
        localDateTime = "Invalid Timezone Operator"
    }
    return localDateTime
}

4. Replaced the line in my js code to convert from utc to local time

Replaced

moment.utc(moment(dateStringFromController).format('YYYY-MM-DD HH:mm:SS')).local().format('MM/DD/YYYY h:mm A');

With

utcToLocal(dateStringFromController, moment.tz.guess())