2

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?

Jeff Dege
  • 11,190
  • 22
  • 96
  • 165

2 Answers2

3

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.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • In our case, we know which timezone each mobile is in, so we can convert the times we receive from UTC to the mobile's timezone. But it seems to me that there is an issue here that MS needs to fix. – Jeff Dege Feb 22 '16 at 22:25
  • It would be great if you could show a bare-minimum end-to-end demonstration of the problem, from your perspective. Perhaps on GitHub and link here. Thanks. – Matt Johnson-Pint Feb 22 '16 at 23:12
  • Not really feasible - it's the interaction between an IOS mobile app and the Azure database that's losing the offset. – Jeff Dege Feb 23 '16 at 17:08
  • @MattJohnson Someone has a bounty on this same question. Feel like copying it over? http://stackoverflow.com/q/42943319/2333499 – SqlZim Mar 24 '17 at 18:52
  • 1
    @SqlZim - Not the same issue, but I can answer that one there. Thanks for the heads up. – Matt Johnson-Pint Mar 24 '17 at 19:17
0

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.

David Yee
  • 3,515
  • 25
  • 45