Grids and Dates

[Salo Corgan]

Hi there.

I'm returning a model to my grid, via ajax, that has a DateTime.

In my database, the dates are UTC.  Each user has an associated timezone.  So I convert the date to the user's local time on the server when I create the model for the grid.

The time in the db is: 2010-07-14 12:52:09.337  (UTC)
The local time in the model is therefore: 2010-07-14 13:52:09.337  (BST - i.e. UTC + 1)  (This is the date I want presented)

The time that ends up in the JSON is:
"/Date(1279119137000)/"

And the time that ends up getting displayed in the browser is:

14/07/2010 15:52:17

In other words, 2 hours away from the time I provided in the model.

The browser's machine is in BST timezone.

I'd really appreciate any help understanding why this is going wrong. Or any pointers as to how this /Date(x)/ format works.  Does the browser end up adding its own timezone offset?

Many many thanks.

[Salo Corgan]

Anybody? 

[Simon Hazelton]

I have the same question asked in the Beta forum, I haven't had an answer there either.

It appears (to me) that telerik.common is modifying the date somehow, which is very frustrating. I have a US based server and my datetimes are displaying 6 hours in to the future.

[Salo Corgan]

I can't believe this is escaping their attention.  Any non-trivial "enterprisey" app would have this issue.  

Why no reply Telerik?

[Atanas Korchev]

Hi guys,

telerik.common.js does not make any modifications to the serialized dates. Basically ASP.NET MVC serializes dates via the builtin JavaScriptSerializer class. You can search for the /Date()/ format used by Microsoft for serializing dates. Here is the telerik code which makes a date out of the serialized value:

return data.replace(rdate, 'new Date($1)');

where rdate is this:
var rdate = /"\\\/Date\((.*?)\)\\\/"/g;

and data is the JSON string returned by the server. This means we are using the new Date(milliseconds) //milliseconds since 1970/01/01 constructor. According to w3cschools this constructor is creating the date in UTC time.

I am not sure if this helps but this is the best help I can provide without seeing some example. You can try experimenting with DateTime serialization without the grid and see if the behavior is any different.

Regards,
Atanas Korchev
the Telerik team

[Simon Hazelton]

Hi Atanas

This makes absolutely no sense.

I've attached a couple of png's showing the problem, the code is as my post in the beta forum.

The grid png shows the grid adding 6 hours to the date displayed in the details.png

The details are called up via an ajax request and in the DB the dates are the same. I really appreciate your help with this.

Simon

[Salo Corgan]

Hi Atanas,

Thanks very much for replying.

The trouble is really that the javascript data object doesn not know anything about timezones really.

Try this on a browser on a machine with GMT timezone:
alert( new Date(1000))

You'll get back something like: 1 Jan 1970 01:00:01

If you change your timezone to +3, you'll get back
1 Jan 1970 03:00:01

So the serialization is correct, but only in a sense.  The browser goes ahead and interprets the date not as UTC, but as local.

The net effect is you're at the mercy of the client's browser if you use the Date object.

I think this is a well known problem (see  http://corneliusweiss.de/2008/10/14/dealing-with-time-zones-in-javascript/ for example)

Sorry for hasty reply - back later.

[Atanas Korchev]

Hello,

@Salo
Have you tried leaving your server date as it is (UTC)? Perhaps then the browser will correctly add the time zone.

@Simon
What makes no sense? I said quite a few (IMHO correct) yet you dismiss them all. Could you point out what exactly I said wrong? Also what are your screenshots showing? Details.png seems to be generated from the server side - is this correct? If yes it is not related with the JavaScript date problem discussed here. I am pretty sure the grid would work as expected if bound server side. Have you tried server binding?

Regards,
Atanas Korchev
the Telerik team

[Simon Hazelton]

Hi Atanas

I'm sure it would work if I used server side binding as well. It seems a little inconsistent for the controls to display two different dates depending on the databinding used though.

Regardless of the technical issues with dates around javascript, I would expect the same information to be shown in the grid (or is that an unreasonable expectation?)

I'm not questioning the correctness of what you have said, you have raised some very valid points, but the behaviour, in my opinion, is not as expected.

[Salo Corgan]

HI Atanas,

Thanks again for the reply.

The problem is this - whatever date you give (using this current method of transferring dates), the browser is going to add it's own timezone markup.

So the server can be UTC (In fact, it is) and give a UTC date in the model , but if the browser is in +8, it will modify it by 8 hours before the grid displays it. 

As far as I can see, this is inescapable due to what I said earlier.  new Date(x) is not really absolute. It's UTC in theory, but the browser timezone, which can be different for every user, changes it.

So this doesn't really work if you can't rely on your users to all have the timezone setting, which noone really can.

The only real solution to this issue is for the server to modify from UTC to a per-user preference for timezone.  But this relies on the grid then displaying *exactly the date given in the model*.  Which as we've seen, it doesn't.

Of course serializing as a string would work and do exactly that, but then we lose the  sorting and filtering functionality.  Binding the other way also works (IIRC), but then you lose all the ajax goodness.

Does that make sense?

I think the only real answer here is to change the grid's deserialization to be aware of this issue. 

[Atanas Korchev]

Hi Simon,

The grid does not know in what timezone the developer has the dates stored in the database server.  Since the server and client are in different time zones it is expected in my opinion that the server and browser display dates differently. In server binding the server is in time zone A and formats the dates accordingly. The browser then just displays the output HTML. In ajax binding the server serializes the dates in the format Microsoft has chosen (/Date()/). The grid then uses the JavaScript Date object to interpret the serialized date. However browsers always treat dates as local and apply timezone. Hence the different output. The way I see it there are a few options:

1. For ajax binding serialize the dates in UTC. The grid will properly display the local time thanks to the browser built-in capabilities. The JavaScript date object will add the required timezone. If you don't serialize the time in UTC the browser will still treat it that way and apply the offset. I personally prefer this workaround as it is easier to implement and works correctly.
2. Serialize the date in whatever format it is stored originally. Then use the OnRowDataBound event to properly convert dates. First you need to convert the serialized date to UTC and then add the current timezone offset. There is plenty info in the net how to do this. That manipulation is required because both the server and client are in different timezones which are not UTC.
3. Use server binding and then the browser will display the same time
4. Convert the date on the server side but then use a string property instead of DateTime. This requires creating a ViewModel object and would also impact sorting and filtering. I do not recommend it personally.

So to sum things up - indeed the grid behaves differently when it comes to showing dates. This is due the way browsers treat dates in JavaScript. If you want the client to see times in his timezone make sure you bind the grid with UTC dates (for Ajax binding).

I hope this helps,
Atanas Korchev
the Telerik team

[Simon Hazelton]

Hi Antanas

Thank you so much for your effort in providing such a detailed explanation.

All of your solutions will work as you have specified, it basically leaves three choices though.

• Use server binding and lose all of the Ajax facilities of the grid.
• Use Ajax binding and have the vagaries of the browser timezones change the date on the grid.
• Write a load of javascript to circumnavigate the handling of date creation on the grid.

I am not criticising the implementation of this, but if I have a fixed date/time on the server it seems like I cannot use Ajax binding which is one of the most attractive features of the Telerik MVC suite. 

I agree, there are workarounds available and you have come up with some great solutions. I now need to ask my users whether it is acceptable to either lose all the Ajax functionality, have dates displayed in the format 2010-08-01 19:33:25Z or if they are prepared to fund development of a different solution.

Once again, thank you for your assistance.

[Atanas Korchev]

Hello Simon Hazelton,

Can't you just use option 1) which is to serialize your dates in UTC and leave the browser to convert them to the current timezone? To me this is the best behavior in terms of user experience. I can only see it not working if you want the user to see the server time precisely as it was originally stored.

About the JavaScript workaround - I would argue that you need to write "loads" of JavaScript. Actually the workaround is pretty simple - a few lines of code. This stack overflow question covers the basics. So here is a working solution involving ClientTemplates which shows how to use Ajax binding and show the *server* local time:

<script type="text/javascript">

    function toServerTime(date) {
        var utcDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate(), date.getUTCHours(), date.getUTCMinutes(), date.getUTCSeconds(), date.getUTCMilliseconds());
        // offset measured in milliseconds
        var serverOffset = 1 /* time zone offset measured in hours */ * 60 * 60 * 1000;
        var serverTime = new Date(utcDate.getTime() + serverOffset);
        return $.telerik.formatString("{0:G}", serverTime);
    }
</script>

<%= Html.Telerik().Grid<OrderDto>()
        .Name("Grid")
        .Columns(columns =>
        {
            columns.Bound(o => o.OrderID).Width(100);
            columns.Bound(o => o.ContactName).Width(200);
            columns.Bound(o => o.ShipAddress);
            columns.Bound(o => o.OrderDate).Width(120).ClientTemplate("<#= toServerTime(OrderDate) #>");
        })
%>

I hope this helps,
Atanas Korchev
the Telerik team

[Simon Hazelton]

Hi Antanas

Again, thanks for your continued efforts!

I'm going to ask what may seem a dumb question...

What exactly do you mean by serialize your dates to UTC?

I've just added this DateTime to my Order Entity and am now binding it to the grid instead of the OrderDate

       

public DateTime OrderDateUTC{           
    get {return this.OrderDate.ToUniversalTime();}
}

and still get the browser offset added.

If I try to convert the date to ticks then the grid picks up the date (in ticks) as a long and not a datetime.

Perhaps I'm being remarkably stupid, it's happened before!

Simon

[Simon Hazelton]

Hi Antanas

I can get the solution you posted working, but can you see where this is going now?

I've implemented the javascript function to display the date correctly.
I'll just quickly knock up some code to make the serverOffset work correctly taking DST into account.
Then I'll work out whether I'm using my dev, test or live environment.

Code to fix code is not good, it ends up as a maintenance headache.

I'm not criticisng anyone but is this really the best solution?

Simon

[Atanas Korchev]

Hello Simon Hazelton,

As I said before the browser is always adding local timezone offset no matter what date you feed it (as ticks). If you feed it with UTC date - the time would display correctly in the local time zone (still with offset). If you feed it with non UTC time - the time will be incorrect after applying the client offset. So what you have seen is correct - when you pass UTC time you always end up with local time. Frankly speaking this is acceptable in some cases - users may like to see the time in their time zone offset not in the server's.

The proposed JavaScript conversion is the only solution I can think of right now. Perhaps hardcoding the server offset is bothering you. However you can easily calculate that offset server side and emit it in the JavaScript:

function toServerTime(date) {
        var utcDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate(), date.getUTCHours(), date.getUTCMinutes(), date.getUTCSeconds(), date.getUTCMilliseconds());
        // offset measured in milliseconds
        var serverOffset = <%= System.TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).Hours %> /* time zone offset measured in hours */ * 60 * 60 * 1000;
        var serverTime = new Date(utcDate.getTime() + serverOffset);
        return $.telerik.formatString("{0:G}", serverTime);
    }

Let me know if this helps or you have a better idea.

Regards,
Atanas Korchev
the Telerik team

[Simon Hazelton]

Hi Again :)

Well, I'm just getting round to catching some zzzzz's and have been rethinking this solution and I may have come up with an idea!

How about we implement your solution as a feature of the product?

Just imagine you could have an extension on the Localizable(en-GB).DateFormat(d => DateTime.ServerTime||DateTime.ClientTime)

I guess it wouldn't be much to emit into the json result and you could then have a page demonstrating, Server Binding, Client Binding with the server dates and Client Binding with the Client Dates, how cool would that be!!!!!

I suddenly feel all warm inside!

Thanks for your help with this

Simon

[Bill]

#1 seems to be the best option for me as well. However, from a data entry perspective, the out of the box behavior seems a bit odd.
I'd like the data values to display and edit in this format

{0:MM/dd/yyyy hh:mm tt}

I tried the OnEdit ClientEvent but am not really sure what I'm supposed to do with te value /Data(13123213)/.

                    <script type="text/javascript">
                        function onEdit(e) {
                            alert(e.dataItem.BargeActivityDateTime);
                            $(e.form).find('#BargeActivityDateTime').val(12);
                        }
                    </script>

[Billy McCafferty]

+1 for Simon Hazelton's feature suggestion.

[Artūras]

Hi, Atanas Korchev

You must use getUTCDate instead of getUTCDay, because getUTCDay returns Day of the week (integer – 0-6)

 

[Atanas Korchev]

Hello Artūras,

 You are absolutely right - it should be getUTCDate(). I have updated my reply to avoid confusion. Thanks a lot for pointing this out!

Regards,
Atanas Korchev
the Telerik team

[fakher]

Thanks for your wonderful guidance. This could solve my problem too. However, I also wanted to format it. It only works when it is first rendered.

c.Bound(t => t.Time).Title("Sample Time").Width(100).ClientTemplate("<#= toServerTime(Time) #>").Format("{0:HH:mm MM/dd}");
:
:

.DataBinding(dataBinding => dataBinding.Ajax().Select("_CustomMethod", "Home")

 

"_CustomToolBar", "Home"))

However, when I call the controller through Ajax, the formatting is lost, and I get full date and time, unformatted. How can I preserve the formatting -- will I have to do that in Java Script? Please guide me on that.

[Atanas Korchev]

Hello,

 Yes, the formatting needs to be done client-side. You can customize the format used in the last line of the toServerTime JS function.

Regards,
Atanas Korchev
the Telerik team

[Andrei]

Just for your information "toServerTime" function works fine except for the following date "10/15/2008 4:46:41 AM" when requested from GMT+4 (Moscow time). That's because  "TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).Hours" returns "4" today, but it returned "3" three years ago (2008). There's no daylight time in Russia anymore (since 2011).

[Andrei]

Hi,

You're using the following C# code <%= System.TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).Hours %>

But do you know any equivalent java-script function?

[Andrei]

By the way, your C# code is broken for offsets that aren't a multiple of a full hour. 

[Hongwei Shen]

Atanas,

I think the toServerTime function has problem with the daylight saving. If the parameter date is in daylight saving and the running time is not in daylight saving or the opposite, then there will be a hour difference in the calculated time. 

I modified the function to compensate the daylight saving but still feel very tentative.

    function toServerTime(date) {
        var utcDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate(), date.getUTCHours(), date.getUTCMinutes(), date.getUTCSeconds(), date.getUTCMilliseconds());
        // offset measured in milliseconds
        var serverOffset = @(System.TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).Hours);
        var dateOffset = -1 * utcDate.getTimezoneOffset() / 60;
        var now = new Date();
        var nowOffset = -1 * now.getTimezoneOffset() / 60;        
        var offset = (serverOffset + (dateOffset - nowOffset)) * 60 * 60 * 1000;
        var serverTime = new Date(utcDate.getTime() + offset);
        return $.telerik.formatString("{0:yyyy-MM-dd HH:mm:ss}", serverTime);
    }

[Obay]

I have another problem in grids and dates, in localized application popup-editing places unlocalized value in localized calender, which causes validation problems, they told me that there is no workaround for that, and i have to modify the source code.
Check  this.

[Alex]

I wanted to show the date in UTC.  After hours and hours of struggling I ended up creating this javascript function:

//based on http://www.telerik.com/community/forums/aspnet-mvc/grid/grids-and-dates.aspx
//formats the date in UTC
function FormatUTCDate(date) {
    if (date == null) return "";
    var utcDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate());
    return $.telerik.formatString("{0:yyyy-MM-dd}", utcDate);
}

with the following client template

.ClientTemplate("<#= FormatUTCDate(myColumnUtcDate) #>")


I bind to a column that's already in UTC. MAKE SURE YOUR DATE KIND IS SET TO Kind.Utc ON THE SERVER OTHERWISE THIS WILL NOT WORK!!

[Mac]

Atanas,

This is a great solution, however there's one problem.

How do we also 'fix' the Group Header datetime field display, when grouping, so that it displays the 'corrected' datetime?

[Atanas Korchev]

Hi Calvin,

 You could try using the ClientGroupHeaderTemplate and format the date as you want it. This online demo shows how to do that: http://demos.telerik.com/aspnet-mvc/grid/aggregatesajax 

Regards,
Atanas Korchev
the Telerik team

[Mac]

Hey Atanas,

I did try that by just applying the toServerTime method in this thread, however the Key attribute apparently isn't a real datetime. Can you post a working example of how to accomplish that?

Another observation, without changing anything on the grid, this is what we experienced:

If you set your datetimes to midnight so that the date picker filtering works correctly (e.g. 5/22/2012 12:00 AM EST), and then you have a user in another timezone, such as CST visit the application.then they will see dates that are 5/21/2012 (since the 'converted' client time is minus 1 hour). But when they attempt to filter for 5/21/2012, they will not get the 5/21/2012 dates back because the server is filtering on EST, not CST.

In other words, because the grid has a datetime mismatch in its own implementation, it isn't working correctly out of the box, irrespective of the technical 'expected' behavior, which I have a differing opinion on (see below).

Effectively, I want to echo the other comments on this thread.

1. It should be up to the business how the grid presents the dates (and using Server Binding isn't the correct answer), not the controls
2. It should be possible via a setting to force the grid to present server datetime if that's the preference or present client datetime if that's the preference.

[Petur Subev]

Hello,

For an implementation which keeps the DateTime in UTC on both client and server sides take a look at this code library.
@Calvin use the approach shown there to set the ClientGroupHeaderTemplate.

Kind regards,
Petur Subev
the Telerik team

[Alex]

Petur,

I tried to follow the example, but OnRowDataBound does not fire for the group header, so there is no way to change it.  I do have access to the group header through ClientGroupHeaderTemplate, but as Calvin mentioned, the Key attribute is a string in local time, not a DateTime object in UTC as I would expect, which would require a conversion to a local datetime object on the client before any formatting can be applied.  I verified that GridModel.Data[0].Key is DateTime object with DateTimeKind.Utc returned from the controller.

Alex

[grimble67]

This is an old thread but I just ran into this problem and I have a simple and somewhat elegant solution. It doesn't require client-side javascript, and you can keep your filtering and sorting as dates and not strings.

Say your model contains "CreateDate", which is a DateTime. Add a new property and call it "CreateDateLocal". Set this to CreateDate.ToShortDateString().

In your view, use this syntax to write the column:

columns.Bound(d => d.CreateDate).ClientTemplate("<#= CreateDateLocal #>");

I have tested this approach and it works both locally on my development machine and in the Cloud.