CODEWITHSUNDEEP
sql-serverazuredatetimeoffset
Jeff Dege
Jeff Dege

Reputation: 11720

Why does Azure lose timezone information in DateTimeOffset fields?

We're working on a IOS app using Microsoft's Azure Mobile Services. The web GUI creates date-time as DateTimeOffset fields, which is fine. But when we have the mobile put datetimes into the database, then read them from the database, via Entity Framework, we're seeing them adjusted to UCT. (We see the same thing when we view the records in SSMS.)

I've always been frustrated by the lack of timezone support, in SQL's standard datetime types, and I'd thought that DateTimeOffset would be better. But if I wanted my times in UTC, I'd have stored them in UTC. If a user enters a time as 3:00 AM, CST, I want to know he entered CST. It makes as little sense to me to convert it to UTC, and throw away the offset, as it did to assume that 3:00 AM CST and 3:00 AM PDT were the same.

Is there some kind of database configuration I can do to keep the Azure database from storing the dates in UTC?

Upvotes: 2

Views: 1213

Answers (2)

David Yee
David Yee

Reputation: 3656

We're using a Node.js backend and noticed the same thing with DATETIMEOFFSETs read from our SQL Server database being returned in UTC regardless of the offset. Another alternative is to convert the DATETIMEOFFSET at the query-level so that it is outputted as a string with the timezone information. The following converts a DATETIMEOFFSET(0) field to the ISO8601 format; however, other possible styles can be used as documented here:

SELECT CONVERT(VARCHAR(33), [StartDate], 126) AS [StartDate] FROM [Products];

The new output is now: "2016-05-26T00:00:00-06:00" instead of "2016-05-26T06:00:00+00:00"

Of course, this means that the client must serialize the string into their respective format. In iOS, the ISO8601 library can be used to read the output as either a NSDateComponents or NSDate.

One benefit of this approach is that any database-level checks or triggers can do date comparisons using the DATETIMEOFFSET instead of trying to take into account a separate offset column with a basic DATETIME.

Upvotes: 0

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241808

The issue is that at some point in Azure Mobile Services, the property is converted to a JavaScript Date object, which cannot not retain the offset.

There are a couple of blog posts describing this issue, and possible workarounds:

Essentially, they both take the same approach of splitting out the offset into a separate field. However, looking closely at these, they both make a crucial mistake:

dto.DateTime.ToUniversalTime()

Should actually be:

dto.UtcDateTime

The DateTime of a DateTimeOffset will always have DateTimeKind.Unspecified, and thus ToUniversalTime will assume the source is local, rather than using the offset of the DTO.

There are a few other similar errors I see in the code in these posts, so be careful to test thoroughly. However, the general approach is sound.

Upvotes: 3

Related Questions