locked
DateTimeOffset always inserted as UTC time with 0 offeset RRS feed

  • Question

  • I tried with both 0.2.0 and latest 0.3.x-rc candidate Azure mobile services SDK. I have a datetimeoffset(3) column in SQL Azure table with Azure Mobile Services. I create a value in C#: DateTimeOffset dt = DateTimeOffset.Now, and when I insert to table via Azure Mobile Services, the value is inserted in SQL Azure table, offset is lost, value is converted to UTC. I need to keep the offset, what's wrong?

    private DateTimeOffset _activityTime;

    [DataMember(Name = "activityTime")]
    public DateTimeOffset activityTime
    {
     get { return _activityTime; }
     set
     {
      SetValue(ref _activityTime, value, new string[] { "activityTime" });
     }
    }

    ...

    activityTime = DateTimeOffset.Now;

    // shows correct datetime +07 offset

    ...

    // when inserted to SQL Azure table offset is lost, value converted to UTC time

    • Edited by kevin_ash Friday, May 3, 2013 10:13 PM
    Friday, May 3, 2013 10:10 PM

All replies

  • Hi Kevin,

    I believe we always insert the time converted to UTC.  I will double check however and get back to you.

    -Jeff


    Jeff Sanders (MSFT)

    @jsandersrocks - Windows Store Developer Solutions @WSDevSol
    Getting Started With Windows Azure Mobile Services development? Click here
    Getting Started With Windows Phone or Store app development? Click here
    My Team Blog: Windows Store & Phone Developer Solutions
    My Blog: Http Client Protocol Issues (and other fun stuff I support)

    Monday, May 6, 2013 2:28 PM
  • Hi Kevin,

    I confirmed that we will always convert to UTC and store.  You would need to do some special handling in your insert script to generate and save this information from the string and then restore it on the way out. 

    Can you tell me the specific case why UTC will not work for you?

    -Jeff


    Jeff Sanders (MSFT)

    @jsandersrocks - Windows Store Developer Solutions @WSDevSol
    Getting Started With Windows Azure Mobile Services development? Click here
    Getting Started With Windows Phone or Store app development? Click here
    My Team Blog: Windows Store & Phone Developer Solutions
    My Blog: Http Client Protocol Issues (and other fun stuff I support)


    Tuesday, May 7, 2013 5:18 PM
  • Kevin,

    As Jeff mentioned, you can preserve the offset for the DTO objects by making some changes in the client and server scripts. The main issue is that by default, DTOs are received by the service runtime as JavaScript dates, which don't have any concept of offset, so at that point, the information is already lost.

    I just blogged with a couple of ways to preserve the offset information. You case may be a little different, in that you already have one column typed 'datetimeoffset(3)', so that exact solution may not work for you. But you can use something similar: use a converter which serializes the DTO value as a string with some prefix (say, 'DTO-'yyyy-MM-dd HH:mm:ss.fff [+/-]hh:mm), and at the server side you could use either try removing the prefix and inserting the value as a string and check that the node.js sql driver which is used on the backend would do the right conversion (I guess it does) or even use the mssql object to send the string with the appropriate CONVERT in the value.

    Hope this helps,
    Carlos.


    Carlos Figueira

    Monday, May 13, 2013 10:04 PM