CODEWITHSUNDEEP
sqlentity-frameworkazureentity-framework-6
atreeon
atreeon

Reputation: 24157

Entity Framework Azure SaveChanges - datetime, incorrect by one hour

I'm using EF6. I get a record, then I update it. One of the columns is a datetime column but it updates it incorrectly. The sql that Entity Framework produces appears correct but when the sql is executed the hour is incorrect in the database. It seems to update the other columns properly.

Whether the fault lies with sql, EF or Azure I'm unsure?

Here is my EF log

UPDATE [dbo].[Fixtures]
SET [FixtureExternalId] = NULL, [SoccerFixtureId] = @0, [HomeTeamId] = @1, [AwayTeamId] = @2, [Date] = @3, [HomeScore] = @4, [AwayScore] = @5, [FixtureStatus] = @6, [InPlayTime] = @7, [CompetitionId] = @8, [CompetitionStage] = @9 WHERE ([FixtureId] = @10)
-- @0: '338066' (Type = Int32)
-- @1: '409' (Type = Int32)
-- @2: '842' (Type = Int32)
-- @3: '23/07/2014 16:00:00' (Type = DateTime2)
-- @4: '3' (Type = Int32)
-- @5: '0' (Type = Int32)
-- @6: '3' (Type = Int32)
-- @7: '89'' (Type = String, Size = -1)
-- @8: '15' (Type = Int32)
-- @9: '0' (Type = Int32)
-- @10: '12467' (Type = Int32)
-- Executing at 23/07/2014 18:31:37 +01:00

-- Completed in 34 ms with result: 1

As you can see of the update statement says 15:00 but after this sql has been executed and I do a sql select of the record I get 2014-07-23 15:00:00.000

Why is the time one hour out?

Thanks

Upvotes: 0

Views: 911

Answers (2)

cchdev
cchdev

Reputation: 511

It is true SQL Azure databases use UTC timezone (see this post). You can verify it by running SELECT GETDATE(), which will return UTC date and time.

It also seems your application uses UTC +1 timezone (based on your EF log: "Executing at 23/07/2014 18:31:37 +01:00").

However, as long as you are using System.DateTime in your code and DATETIME / DATETIME2 in your database, the above assertions are irrelevant. In fact:

  • SQL DATETIME / DATETIME2 do not store timezone information. So, by design, both are timezone agnostic. No timezone offset on the database server itself is applied, according to this comment.

  • EF6 is also timezone agnostic. After browsing the source code, I found EF6 ignores the Kind property of System.DateTime values, and never calls ToLocalTime, ToUniversalTime, nor any other timezone offsetting method. The log formatter is also reliable, as it is a simple call to object.ToString.

Now back to your questions:

Whether the fault lies with sql, EF or Azure I'm unsure? Why is the time one hour out?

The most likely answer is that the fault relies neither with SQL, EF nor Azure. Here are a few other possibilities:

  • The value was updated again before observation
  • If observing the value using a SQL management tool like SQL Data Tools, maybe the tool is applying some conversion at display
  • A trigger applies some transformation of the [Date] column
  • The observed value (2014-07-23 15:00:00.000) is the result of a call to DateTime.ToLocalTime or DateTime.ToUniversalTime methods after it was loaded from the database
  • The value you observed is offsetted by some JSON or XML serialization/deserialization (see this post > The Special Case of XML) after it was loaded from the database

Hope this helps.

Upvotes: 2

viperguynaz
viperguynaz

Reputation: 12174

Use datetimeoffset in ISO 8601 format to mantain local: YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] for an offset or YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z for UTC (Azure works in UTC time).

In code, you can try DateTime.ToLocalTime or DateTime.ToUniversalTime

Upvotes: 0

Related Questions