UTC to Local Datetime in Grid

[Andrew]

Hi,
I using the Grid control and have a datetime field. I am trying to understand how to update my database with the correct datetime.
The database and website is hosted on a server that is in UTC time. My Grid shows the correct datetime and the client is in Australia. I have setup culture to en-AU and this is working correctly but when I update the database the datetime field is wrong by the timezone of +11 hours.
The Grid always shows the datetime correctly eg. database will have 16/10/2014 12:00:00 AM and it will show in the Grid as Thu Oct 16 2014 11:00:00 GMT+1100 (AUS Eastern Daylight Time). 
When I update the Grid it will put into the database 16/10/2014 11:00:00 AM but show in the Grid Thu Oct 16 2014 22:00:00 GMT+1100 (AUS Eastern Daylight Time)

I then tried the example http://www.telerik.com/support/code-library/using-utc-time-on-both-client-and-server-sides
but this always show in the Grid as UTC time and I want to display the datetime in the local time.
eg. The database has 16/10/2014 12:00:00 AM and the Grid shows Thu Oct 16 2014 00:00:00 GMT+1100 (AUS Eastern Daylight Time). Updating the Grid does the same. 

Is there another way of doing this?
Thanks.

[Vladimir Iliev]

Hi Andrew,

Basically when the dates from the server response are deserialized on the client side they are always offset with the current client offset (timezone) - that why in order to correctly show and update dates you should choose one of the following options:

• Keep UTC dates on both the client and the server side. This approach would work for clients across different timezones (demonstrated in the linked CodeLibrary demo).
• Keep UTC dates on the server side and specific timezone on the client side. This can be achieved using the linked CodeLibrary example as baseline (all conversions from and to UTC should be done on the client side) - first offset the received date to UTC and then apply the needed timezone offset (AUS Eastern Daylight Time in current case).

Regards,
Vladimir Iliev
Telerik

 

 

[Ian]

Hi Vladimir,

I am having the exact same problem as Andrew. In your reply you mention two 'linked CodeLibrary' demos but where can I find these.

Thanks

[Vladimir Iliev]

Hi Ian,


Please check the link to the example below:

• Using UTC dates on both the client and server side

Regards,
Vladimir Iliev
Telerik

 

 

[Ian]

Vladimir,

if I had a dollar for every time some blog, forum or Telerik support response sent me to this link then I would be a rich man and could afford to buy a decent web development platform and dump Telerik completely !! I have been working with the product for nearly 6 months now and every day is a new battle to get it to do what I want.

The link provided DOES not answer the original question from Andrew and does not provide a solution.

In my MVC solution:

• The DateTime data is stored in a database as UTC
• Using the 'Server' part of the linked demo and specifying a DateTimeKInd of UTC, the datetime data is sent to a Kendo UI grid in UTC format.
• By some black magic that I don't fully understand, the Browser or the grid or Javascript take the DateTime in UTC and apply the correct timezone correction. The DateTime gets displayed in the local time of the Browser. Which is fantastic once you realise you get this functionality for free.
• When you try to Edit the same information using the built in in-line or in-cell Editing function, what gets sent back to the MVC controller is the DateTime still in the Local time of the Browser. It needs to be in UTC time so the Controller can deal with it appropriately and store it in the database as UTC.
• What process do I need to adopt so that the DateTime information is 'intercepted' at the Edit event, converted back to UTC and then sent up to the Controller ?
• The linked demo provided 'Client' side portion only results in the DateTime information being displayed in the grid in UTC and not in Local time and does not answer the original question.
• The requirement is simple. The grid, upon receipt of UTC datetime information, should display DateTime information in Local time (which it does), but to provide DateTime information in UTC format, not Local format, when Editing.

Thanks
Ian

[Vladimir Iliev]

Hello Ian,

The "magic" that converts the dates on the client side is the JavaScript implementation in all browsers (already mention in my initial post). If you need to convert the dates only before sending them to the server side you can for example specify "Data" function of the "Update" / "Create" actions of the DataSource:

.Update(up => up.Action("UpdatePerson", "Home").Data("offsetDateFields"))

var offsetMiliseconds = new Date().getTimezoneOffset() * 60000;

function offsetDateFields(data) {
    for (var field in data) {
        if (data[field] instanceof Date) {
            data[field] = new Date(+data[field] + offsetMiliseconds)
        }
    }
}

Regards,
Vladimir Iliev
Telerik

 

 

[Leo]

Is this still the recommended way?

I find it a hassle to get something so trivial working. I completely agree to Ian. The grid should use UTC in all data transmission and localtime in the UI (maybe configurable similar as the kendo.culture setting.

I had to add this Data method to all datasource (Create and Update) methods for all pages. Some of them allready had a data method that makes it even more complex and less maintainable. 

 

 

The Create, Update and Delete methods look similar to the one below. Conversion from db to view is done within the viewmodel, but because a validation error may occur i have to do this conversion within each CUD method too for every field for every controller. Again it is hard to maintain and easy to introduce bugs. Don't think Telerik can fix the viewmodel => viewmodel part i describe below. Any suggestions on that?
       

public ActionResult Booking_Update([DataSourceRequest]DataSourceRequest request, VMBooking viewmodel)
{
    if (viewmodel != null && ModelState.IsValid)
    {
        var entity = (Booking)viewmodel;

        db.Booking.Attach(entity);
        db.Entry(entity).State = EntityState.Modified;

        try
        {
            db.SaveChanges();
        }
        catch (Exception ex)
        {
            ModelState.AddModelError("updateerror", ex.GetOriginalException().Message);
        }
    }
    //all viewmodel date fields are set to unspecified (effectively localtime) while you know it is UTC
    viewmodel.StartTime = DateTime.SpecifyKind(viewmodel.StartTime, DateTimeKind.Utc);
    viewmodel.EndTime = DateTime.SpecifyKind(viewmodel.EndTime, DateTimeKind.Utc);
    viewmodel.EntryDate = DateTime.SpecifyKind(viewmodel.EntryDate, DateTimeKind.Utc);
    viewmodel.FirstStateChange = DateTime.SpecifyKind(viewmodel.FirstStateChange, DateTimeKind.Utc);

    return Json(new[] { viewmodel }.ToDataSourceResult(request, ModelState));
}

 

 

 

 

[Viktor Tachev]

Hello Leo,

 

Out of the box the Kendo components do not perform conversion of the data. Thus when the browser applies an offset to a date that will be shown in the Grid. The article below describes how you can apply UTC on both the client and server.

https://docs.telerik.com/aspnet-mvc/html-helpers/data-management/grid/how-to/editing/utc-time-on-both-server-and-client

 

Regards,
Viktor Tachev
Progress Telerik

[Anita]

We are following the client side conversion. Still some DOBs are off by a day. 

Here is the copy of our codes

Grid Viewer:

<script src="~/Scripts/Custom/TimeZoneDateFix.js"></script>

Grid Column

 col.Bound(c => c.BirthDate).Width("100px").HeaderHtmlAttributes(new { @class = "tooltipCol", @title = "Birth Date" });

 

TimeZoneDateFix.js

function onRequestEnd(e) {
    if (e.response) {
        if (e.response.Data && e.response.Data.length) {
            var data = e.response.Data;
            if (this.group().length && e.type == "read") {
                handleGroups(data);
            } else {
                loopRecords(data);
            }
        }
    }
}

function handleGroups(groups) {
    for (var i = 0; i < groups.length; i++) {
        var gr = groups[i];
        offsetDateFields(gr); //handle the Key variable as well
        if (gr.HasSubgroups) {
            handleGroups(gr.Items)
        } else {
            loopRecords(gr.Items);
        }
    }
}

function loopRecords(records) {
    for (var i = 0; i < records.length; i++) {
        var record = records[i];
        offsetDateFields(record);
    }
}

function offsetDateFields(obj) {
    for (var name in obj) {
        var prop = obj[name];
        if (typeof (prop) === "string" && prop.indexOf("/Date(") == 0) {
            obj[name] = prop.replace(/\d+/, function (n) {                
                var offsetMiliseconds = new Date(parseInt(n)).getTimezoneOffset() * 60000;
                return parseInt(n) + offsetMiliseconds
            });
        }
    }
}

function excelExport(e) {
    var columns = e.workbook.sheets[0].columns;
    columns.forEach(function (column) {
        delete column.width;
        column.autoWidth = true;
    });
}

[Alexander]

Hi Anita,

Could you please consider replicating the behavior in the following sample and send it back for further examination?

This would be of great help to the case. Notice, that you have to manually include a "Kendo.Mvc.dll" file within the lib folder. In order for the application to be fully operational.

[Anita]

The sample code helped resolving the issue.

The issue was  missing "-?" in  the JQuery function causing an incorrect date translation for dates prior to 1/1/1970.

Replacing "obj[name] = prop.replace(/\d+/, function (n)" with  "obj[name] = prop.replace(/-?\d+/, function (n)"  resolved the issue.