We help IT Professionals succeed at work.

Does Entity Framework sometimes auto-convert DateTime to EST?

curiouswebster
curiouswebster used Ask the Experts™
on
Does Entity Framework sometimes auto-convert DateTime to EST?

My Javascript call is making an MVC call to a C# web method with a date that is set to midnight. But, when I query the database, I see the correct date was added to the DB table, as expected. However,  there is time added, and it turns out to be UTC time:

I am on the East Cost of the US and am currently 4 hours behind UTC/GMT.

Here is the format of the data:

2019-11-01 04:00:00.000

I was expecting:
2019-11-01 00:00:00.000

Since, the JSON date/time I am passing in was at midnight.

Is there a setting in Entity Framework which may cause this?

Or, is there a JSON parser doing this?


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
Try to call ToLocalTime() DateTime method before saving the data to DB
curiouswebsterSoftware Engineer

Author

Commented:
I do not want local time. I want a date at midnight to be stored as midnight and not adjusted to be on UTC.
Eduard GherguArchitect - Coder - Mentor

Commented:
Ok, so where is the problem? The data that is comming from the UI is correct? The data that it's stored in the db is correct?
curiouswebsterSoftware Engineer

Author

Commented:
The data that is coming from the UI is correct? Yes.
The data that it's stored in the db is correct? No.

The data is stored correctly, but the time is UTC.

The Javascript send the following date to the back-end
11/2/2019 00:00:00

I verify in the Web Method, by logging to a text file, the date received in the web function is:
11/2/2019 00:00:00

But, the date stored in the DB is:
11/2/2019 04:00:00

For the 4 hours' difference from UTC. (I am EST)
The date that gets
Eduard GherguArchitect - Coder - Mentor

Commented:
Ok, how do you handle the data received from the UI?
curiouswebsterSoftware Engineer

Author

Commented:
It's simple. I want the date/time stored to the DB exactly as the UI passes it into the web method. I need to uncover if Entity Framework is the cause of this manipulation.

Do you know if EF has preferences which govern such changes?
Duy PhamFreelance IT Consultant
Commented:
Hi curiouswebster,

My guess is that your database column uses DATETIME type which doesn't hold timezone offset. So usually when saving datetime values from webserver to database, datetime values would be converted to UTC. I don't think this is related to EF or any other ORM as well as any data access libraries.

But that's also applied when reading datetime values from databases, they will be loaded into local time of the web server. So may I ask why do you need to store the exact local time into database?

If you still want to do that, maybe you can change to use DATETIMEOFFSET.
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
I understand what you want to achieve, I need to see the code that you're using to store the data received from the UI to DB. EF is not the quilty party
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I verify in the Web Method, by logging to a text file, the date received in the web function is:
11/2/2019 00:00:00

Kindly run SQL Profiler trace to confirm whether your application passed 11/2/2019 00:00:00 or 11/2/2019 04:00:00 in the Database call..
That would be best way to narrow down the issue..
curiouswebsterSoftware Engineer

Author

Commented:
Hi, I executed the following:

update milestone set psd1_date='12/1/2019' where project_id=1234
select ms.psd1_date from milestone ms where project_id=1234

Open in new window


and got the return value:
2019-12-01 00:00:00.000

So, the database does not seem to move the time.
curiouswebsterSoftware Engineer

Author

Commented:
> If you still want to do that, maybe you can change to use DATETIMEOFFSET.

This sounds like it may be the problem. My server are based on EST which perfectly explains a UTC offset.

How do I check the value the DB uses for DATETIMEOFFSET?

And how would I change that value?
curiouswebsterSoftware Engineer

Author

Commented:
It can not be the database making this change...

The date is set by the Javascript and passed to the back-end as part of the milestoneParamValue array.

var saveMilestones = 'https://enterpriseservices-web.com/PServices/PService.svc/SaveMilestones';

   var client1 = odataClient.createXhrClient(saveMilestones, false).create(milestoneParamValue).success(function (response, status) {

Where a breakpoint in the browser (on createXhrClient above) shows the date has the value:

"/Date(1573016400000)/"

which translates to:
Wed Nov 06 2019 00:00:00 GMT-0500 (Eastern Standard Time)

But...

On the first line of the web method (SaveMilestones):

I write the value of that time variable to a text file.  I see it in UTC and not EST.

PSD1_date A: 11/6/2019 5:00:00 AM

So, it seems the change is taking place here:
        [WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]

Are there preferences for this JSON format?
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,

What the settings for the JSON serializer? If they're not specified, you can override these settings or part of them.
curiouswebsterSoftware Engineer

Author

Commented:
How do I find where those setting are defined?

I use C#.NET and MVC..

I see the following above the web method...

        [WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]

And how would I over-ride them?
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi! Which version of MVC? Which version of .Net Framework?
curiouswebsterSoftware Engineer

Author

Commented:
.NET Framework, 4.5. I am not sure of the MVC version.

I am glad you brought up the topic of JSON settings. I found one which I need to experiment with:

https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_DateTimeZoneHandling.htm

The default, "RoundtripKind", has a value of 3 and means "Time zone information should be preserved when converting"

How do I alter the value of this setting?

Is that possible? I searched my entire C# solution and found no instance of work "Newton", which tells me WebMessageFormat.JSON is a different animal.

Thanks.
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi!
You can have a look at:
https://www.danylkoweb.com/Blog/create-a-custom-json-serialization-binder-to-resolve-derived-types-ON

But, WebMessageFormat.JSON is specific to WCF services, so it seems that you're not using ASP.NET MVC, or?
curiouswebsterSoftware Engineer

Author

Commented:
I guess you are right. These are Web Methods. Sorry.

I only see the following namespace....
System.Runtime.Serialization.Json;

and suspect that nothing on that URL you provided can help.

I am just looking for how I can over-ride the default settings for date conversion logic.

Any ideas?
Architect - Coder - Mentor
Commented:
Hi,
In the same namespace, there are defined DataContractJsonSerializer and DataContractJsonSerializerSettings classes.
https://docs.microsoft.com/en-us/dotnet/api/system.runtime.serialization.json?view=netframework-4.8
In the DataContractJsonSerializerSettings there is a DateTimeFormat property that you can use to take out the time component from the DateTime value.
Check also https://stackoverflow.com/questions/9266435/datacontractjsonserializer-deserializing-datetime-within-listobject
for some practical discussion and samples.
curiouswebsterSoftware Engineer

Author

Commented:
Thanks
Eduard GherguArchitect - Coder - Mentor

Commented:
You're welcome!