locked
Sync Framework over WCF, SQL 2005, Datetime is shifted by Timezone RRS feed

  • Question

  • I am storing all of my datetime in UTC in the client SQL Compact 3.5 Db and SQL Server 2005  but the datetime seems to be getting serialized to local time when sent across the WCF channel and then deserialized in the clients timezone before getting written to the database. This is a problem as the time is shifted by the difference between timezones betwen the wcf server and the client machine. So if the webserver that is hosting the sync wcf is not in the same timezone as the client the datetime is shifted.  Is there a way to change the way serialization happens in wcf so it stays in utc across the wcf channel?

    Andy
    Thursday, February 12, 2009 4:01 PM

Answers

  • I think you mis-understand the problem. The problem is not with sync services but with the serilization of dataset. The problem is that there is a bug in the Serialization of DateTime values when they are sent across the wcf channel to the client.  

    Here is the KB artical.   http://support.microsoft.com/kb/842545

    You will see this problem if any of the remote clients are in a differnt time zone then the wcf server. All date time fields will be shifted by the difference between the time zones.  

    My fix was to add another function to the WCF service to allow the client to tell the server what timezone it is in. Then On the applyChanges and getchanges call shift the date so when they are deserialized on the client they are stored in database as the correct utc time.  Bottom line the XML serializer should be able to look at the kind of a datetime and if it is unkown or utc then it should serlize as utc. If kind is set to local then and only then it should be serlized with the timezone information.

    Andy
    Tuesday, February 17, 2009 2:24 PM

All replies

  • I can't be the only person to run into this problem with Sync Services over wcf and Dates stored in UTC. I see hundreds of posts regarding Datetime over WCF and the same problem. Is there no way to tell the server provider to force all datetimes toUniversalTime. All the fixes I see are to force the datacontract but I am not sure this is posible using the serversyncprovider. The datetimes are UTC in SQL 2005 but for some reason when they are pulled by the Sync Framwork service provider it is defaulting to local time not utc changing all dates for client not in the servers timezone. Why would SyncFramwork convert them to local time if it is unkown in sql server 2005.
    Friday, February 13, 2009 2:46 AM
  • 1.  The datetime field is for user data, right?
    2. What is the .NET version on the WCF server?  I assume this server resides on the same machine as the SQL server 2005.
    3. What is the file version of %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll and 
        %Program Files%T\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.SqlServerCe.dll ?

    Thanks.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Leo Zhou
    Tuesday, February 17, 2009 12:46 AM
    Answerer
  • Hello - more question

    When you mentioned you have Dates stored in UTC, was the datetime value being converted to UTC already then stored in the database or it is in the local datetime value?  In general, if the datetime values stored on the server is X, you should see the same value X on the client side.

    Are you dealing time zone specifically in your code?

    Thanks.
    Leo Zhou --------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 17, 2009 1:46 AM
    Answerer
  • I think you mis-understand the problem. The problem is not with sync services but with the serilization of dataset. The problem is that there is a bug in the Serialization of DateTime values when they are sent across the wcf channel to the client.  

    Here is the KB artical.   http://support.microsoft.com/kb/842545

    You will see this problem if any of the remote clients are in a differnt time zone then the wcf server. All date time fields will be shifted by the difference between the time zones.  

    My fix was to add another function to the WCF service to allow the client to tell the server what timezone it is in. Then On the applyChanges and getchanges call shift the date so when they are deserialized on the client they are stored in database as the correct utc time.  Bottom line the XML serializer should be able to look at the kind of a datetime and if it is unkown or utc then it should serlize as utc. If kind is set to local then and only then it should be serlized with the timezone information.

    Andy
    Tuesday, February 17, 2009 2:24 PM
  • I think you mis-understand the problem. The problem is not with sync services but with the serilization of dataset. The problem is that there is a bug in the Serialization of DateTime values when they are sent across the wcf channel to the client.  

    Here is the KB artical.   http://support.microsoft.com/kb/842545

    You will see this problem if any of the remote clients are in a differnt time zone then the wcf server. All date time fields will be shifted by the difference between the time zones.  

    My fix was to add another function to the WCF service to allow the client to tell the server what timezone it is in. Then On the applyChanges and getchanges call shift the date so when they are deserialized on the client they are stored in database as the correct utc time.  Bottom line the XML serializer should be able to look at the kind of a datetime and if it is unkown or utc then it should serlize as utc. If kind is set to local then and only then it should be serlized with the timezone information.

    Andy
    The KB artical said that bug is apply to
     
    • Microsoft .NET Framework 1.1
    • Microsoft .NET Framework 1.0

    Are you still Sync Framework will cuase impact? 
    As I know we are in .NET framework 2.0 foundation.

    Monday, June 1, 2009 5:58 AM
  • To use WCF you have to be at least .net Framework  3.0 so not sure why the artical says .net 1.1.  Anyways you can do a simple test and setup a wcf channel and send a date time inside of a datatable (This is how Sync Services moves data) you will note that if the client is in a different timezone then the server that the date is shifted to compensate for the time change.  

    The work around I used is to on every connection over the wcf channel have the client send its timezone to the server and the server send its timezone to the client. The before the date fields are inserted into the client or server databases shift them back to UTC.  

    I think its bad practice to store dates in the local client timezone because think of what happens when the clinet moves to a different timezone then changes their timezone setting on their computer  to reflect that.  I always store dates in UTC.

    Again this is only a problem if the server and client are in different timezones.

    Andy
    Monday, June 1, 2009 1:35 PM
  • If we store date time field as UTC before everything been synchronize will the date time shifted issue solve?

    Thursday, July 9, 2009 8:02 AM
  • I am having the same issue with DateTime fiels of a DataTable. Interestingly, when you send a DateTime values from WCF to the client the is no conversion happens. But when you send a DataTable which has some DateTime columns in it all the DateTime values converts to local time of the client automatically.

    Isn't there any other approachs to this problem or is it really a bug?
    Wednesday, September 9, 2009 2:27 PM