Wrong DateTime conversione from Controller to Json data

[sintetico82]

When i use DataTablesResult.Create method with list of objecta which have the date field, the library add two hours to the end result

[mcintyre321]

It's a localisation problem. The lib converts using tolocaltime for current user

On 19 Sep 2016 11:39, "sintetico82" notifications@github.com wrote:

When i use DataTablesResult.Create method with list of objecta which have the date field, the library add two hours to the end result

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/mcintyre321/mvc.jquery.datatables/issues/155, or mute the thread https://github.com/notifications/unsubscribe-auth/AAQ0-h9TUE2bUUYC3NxQt4HgoWfVZyjMks5qrmZngaJpZM4KAWOS .

[sintetico82]

Yes i understand but why set this conversation in automatic? Now after the update of the library every DateTime taked from the database are changed in JSON conversation.

How can disable this feature? For quick fix i downgrade the version of the library.

[mcintyre321]

Hmm. The behaviour shouldn't have changed...

Questions: 1 are you using DateTime or DateTimeOffset on your view model 2 are those in UTC? 3 Are you setting the thread culture to the current users culture? 4 what time zone is the user in 5 what time zone is the server in 6 Are you trying to display a UTC or local time?

On 19 Sep 2016 16:37, "sintetico82" notifications@github.com wrote:

Yes i understand but why set this conversation in automatic? Now after the update of the library every DateTime taked from the database are changed in JSON conversation.

How can disable this feature? For quick fix i downgrade the version of the library.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/mcintyre321/mvc.jquery.datatables/issues/155#issuecomment-248028944, or mute the thread https://github.com/notifications/unsubscribe-auth/AAQ0-m2mi5flm_hOl8T1unMN4i69VYmHks5qrqwdgaJpZM4KAWOS .

[mcintyre321]

Also read this for a workaround:

https://github.com/mcintyre321/mvc.jquery.datatables/issues/153

I definitely want to understand what behaviour you a/ expect and b/ are getting though, please let me know.

[sintetico82]

Hi, Thank you for your answer. I take my date from the database with Entity Framework. When i extract a datatime field the value is the same of the value stored in database. Whe i use DataTablesResult.Create the library transofrm my data adding timezone i think.

1 are you using DateTime or DateTimeOffset on your view model -- DateTime 2 are those in UTC? -- I work with my local time and not UTC. 3 Are you setting the thread culture to the current users culture? -- I set thread culture with user chois from menu with this two line of code: Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(cultureName); Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;

4 what time zone is the user in -- I develop on my pc and time zone are (UTC+01:00) Amsterdam, Berlino, Berna, Roma, Stoccolma, Vienna 5 what time zone is the server in -- I develop on my pc and time zone are (UTC+01:00) Amsterdam, Berlino, Berna, Roma, Stoccolma, Vienna. I store value in Sql Server database in local time format 6 Are you trying to display a UTC or local time? -- Local time

I downgrade the library to 1.3.22 version that not apply transformation of timezone to my DateTime object.

[mcintyre321]

OK, so I think the problem is that your database dates are stored in local time, not UTC, which is not a best practice (see this link).

You get into all sort of trouble if you use local time to store data once you have users in more than one country, and/or your country has Daylight Savings. I recommend you update your database and change all dates to UTC using DATEADD, and fix your code to save dates in UTC.

I also think 1.3.22 had a bug, but that bug is making it work for you!

In the meantime you can workaround with this code:

protected void Application_Start(object sender, EventArgs e) {
{
     StringTransformers.RegisterFilter<DateTimeOffset>(dateTimeOffset => dateTimeOffset.ToString("g"));
     StringTransformers.RegisterFilter<DateTime>(dateTime => dateTime.ToString("g"));
}

Which would normally be useful if your app was all about rendering UTC, but is also useful if you have saved local times in database.

[sintetico82]

Ok thank you for workaround .

In my opinion the library behavior is not correct. My DateTime object has a value and i don't understand why that the library want modify it. It 'a choice of the developer to decide whether or not to change the value of a variable and it should not be an autonomous action of a library on the basis of assumptions that my date is in UTC format.

[mcintyre321]

Glad you got it working.

On the correctness of the library:

Run this in LinqPad:

void Main()
{
    var dateTimeFromDb = DateTime.Parse("09/21/2016 10:40:06");
    dateTimeFromDb.Kind.Dump(); //"Unspecified" <- entity framework doesn't know if it's Local or UTC
    dateTimeFromDb.ToLocalTime().ToString().Dump(); //"09/21/2016 11:40:06" <- has been localised

    var dateTimeAsUtc = DateTime.SpecifyKind(dateTimeFromDb, DateTimeKind.Utc);
    dateTimeAsUtc.ToLocalTime().ToString().Dump(); //"09/21/2016 11:40:06" <- has been localised

    var dateTimeAsLocal = DateTime.SpecifyKind(dateTimeFromDb, DateTimeKind.Local);
    dateTimeAsLocal.ToLocalTime().ToString().Dump(); //"09/21/2016 10:40:06" <- not changed as already localised, this is the behaviour you want
}

The problem is that your app doesn't map the DateTime objects as Local (EF maps them as 'Unspecified' which acts like Utc), if it did, then the ToLocal would behave correctly (i.e. do nothing).

If you are going to continue to use Local times in the db, you should improve your EF mapping code to materialise the DateTimes as Local time, other wise you may find other libraries give you similar issues.