通过

Preserving date time offsets in Azure Mobile Services

  • 项目

In my last post I mentioned that due to the heterogeneous nature of Azure Mobile Services (JavaScript in the server, other languages / frameworks in the client), some data types have some problems with the conversion between the two environments. Numbers and dates are the most common, but this post from the MSDN forum pointed out that DateTimeOffset values also have issues. Basically, DateTimeOffset values are interpreted in the server side as JavaScript Date objects, and as such they don’t have any information about offsets (in JS dates are represented as a number of milliseconds since the “Unix 0”, 1970/01/01:00:00:00 UTC). The result was that the offset was lost in translation, and that was a problem for the forum poster.

There are ways to work around this limitation, however. The simplest alternative would be to split the DateTimeOffset property into two properties in the client side – one for the DateTime itself, and one for the offset from UTC. In this case, the class would have two properties equivalent to the DateTimeOffset property – and the type can even continue with the original property (properly marked with [JsonIgnore] to prevent it from being sent to the server and creating a new column – notice that this code is already using the version 0.3.x of the client SDK, the Portable Library-based SDK from the NuGet feed). The code below shows the before and after version of a type with a DateTimeOffset property.

  1. public class Event
  2. {
  3.     public int Id { get; set; }
  4.     public string Name { get; set; }
  5.     public string Location { get; set; }
  6.     public DateTimeOffset Time { get; set; }
  7. }
  8.  
  9. public class ModifiedEvent
  10. {
  11.     public int Id { get; set; }
  12.     public string Name { get; set; }
  13.     public string Location { get; set; }
  14.     public DateTime UtcTime { get; set; }
  15.     public int UtcTimeOffsetMinutes { get; set; }
  16.  
  17.     [JsonIgnore]
  18.     public DateTimeOffset Time
  19.     {
  20.         get
  21.         {
  22.             return new DateTimeOffset(
  23.                 DateTime.SpecifyKind(this.UtcTime.ToUniversalTime(), DateTimeKind.Unspecified),
  24.                 TimeSpan.FromMinutes(UtcTimeOffsetMinutes));
  25.         }
  26.  
  27.         set
  28.         {
  29.             this.UtcTime = value.DateTime.Kind == DateTimeKind.Unspecified ?
  30.                 DateTime.SpecifyKind(value.DateTime, DateTimeKind.Utc) :
  31.                 value.DateTime.ToUniversalTime();
  32.             this.UtcTimeOffsetMinutes = (int)value.Offset.TotalMinutes;
  33.         }
  34.     }
  35. }

That solution works fine, but it forces us to change the domain object on the client side to deal with a distributed problem. If other people are using this type, they will get affected by it (we can mitigate it by using something like decorating the UtcTime and UtcTimeOffsetMinutes with [EditorBrowsable(EditorBrowsableState.Never)], but that’s again just working around the problem. What we can do is to change the way we serialize the original Time field, so that only during the serialization (and deserialization) of the type we need to worry about deconstructing (and reconstructing) the value. I posted before about how we can do it with the original managed client SDK, and the idea with the new version of the SDK (based on JSON.NET) is similar, as I’ll show below.

First, we don’t need to split the parameter in multiple values – all we need to do is to decorate the property with the [JsonConverter] attribute to tell the client runtime that we want to use our own code to convert that value to and from JSON.

  1. public class Event
  2. {
  3.     public int Id { get; set; }
  4.     public string Name { get; set; }
  5.     public string Location { get; set; }
  6.     [JsonConverter(typeof(DtoPreservingOffsetConverter))]
  7.     public DateTimeOffset Time { get; set; }
  8. }

We can now go about implementing the converter – a type deriving from the JsonConverter class from JSON.NET, with three abstract methods. The implementation of CanConvert is trivial, as shown below.

  1. public class DtoPreservingOffsetConverter : JsonConverter
  2. {
  3.     public override bool CanConvert(Type objectType)
  4.     {
  5.         return objectType == typeof(DateTimeOffset);
  6.     }
  7.  
  8.     public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
  9.     {
  10.         return null;
  11.     }
  12.  
  13.     public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
  14.     {
  15.     }
  16. }

To write the object into JSON we could call the appropriate methods into the JsonWriter parameter, but the simplest way is to wrap them into a dictionary, and let the JSON.NET serializer deal with that for us. What we’re doing here is to write the DateTimeOffset value as a JSON object with two members, one for the UTC value date component, one for the offset component.

  1. public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
  2. {
  3.     DateTimeOffset dto = (DateTimeOffset)value;
  4.     DateTime dt = dto.DateTime.Kind == DateTimeKind.Unspecified ?
  5.         DateTime.SpecifyKind(dto.DateTime, DateTimeKind.Utc) :
  6.         dto.DateTime.ToUniversalTime();
  7.  
  8.     Dictionary<string, object> temp = new Dictionary<string, object>
  9.     {
  10.         { "DateTimeUTC", dt },
  11.         { "OffsetMinutes", dto.Offset.TotalMinutes }
  12.     };
  13.  
  14.     serializer.Serialize(writer, temp);
  15. }

And since we write it, we need to read it back, so here’s the implementation of ReadJson:

  1. public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
  2. {
  3.     Dictionary<string, object> temp = serializer.Deserialize<Dictionary<string, object>>(reader);
  4.     DateTime dateTimeUTC = ((DateTime)temp["DateTimeUTC"]).ToUniversalTime();
  5.     int offsetMinutes = Convert.ToInt32(temp["OffsetMinutes"]);
  6.     return new DateTimeOffset(
  7.         DateTime.SpecifyKind(dateTimeUTC, DateTimeKind.Unspecified),
  8.         TimeSpan.FromMinutes(offsetMinutes));
  9. }

At this point, the client is all set. However, if we try to insert any instance of the Event class into an Azure Mobile Service, we’ll have an error at the runtime – the runtime doesn’t know how to insert objects into the database, so we need to update the insert (and update and read) scripts to “tell” it what to do with those. The full explanation can be found in the post about supporting complex types in mobile services. The insert script would look similar to the one below – if the incoming item has a ‘Time’ property of type object, that (likely) means that our converter is the one supplying the values, so we know which fields to look for. And after the insertion is completed, we recreate the original object, which will then be read by the converter at the client.

  1. function insert(item, user, request) {
  2.     var dto = item.Time;
  3.     if (typeof dto === 'object') {
  4.         delete item.Time;
  5.         item.Time = new Date(dto.DateTimeUTC);
  6.         item.Offset = dto.OffsetMinutes;
  7.     } else {
  8.         // Either null or old clients; assuming object, and setting offset to 0
  9.         item.Offset = 0;
  10.     }
  11.  
  12.     request.execute({
  13.         success: function () {
  14.             // Need to recreate the object back
  15.             var dtoObject = { DateTimeUTC: item.Time, OffsetMinutes: item.Offset };
  16.             delete item.Offset;
  17.             item.Time = dtoObject;
  18.             request.respond();
  19.         }
  20.     });
  21. }

The update handler is exactly the same – break the object before the operation, recreate it afterwards:

  1. function update(item, user, request) {
  2.     var dto = item.Time;
  3.     if (typeof dto === 'object') {
  4.         delete item.Time;
  5.         item.Time = new Date(dto.DateTimeUTC);
  6.         item.Offset = dto.OffsetMinutes;
  7.     } else {
  8.         // Either null or old clients; assuming object, and setting offset to 0
  9.         item.Offset = 0;
  10.     }
  11.  
  12.     request.execute({
  13.         success: function () {
  14.             // Need to recreate the object back
  15.             var dtoObject = { DateTimeUTC: item.Time, OffsetMinutes: item.Offset };
  16.             delete item.Offset;
  17.             item.Time = dtoObject;
  18.             request.respond();
  19.         }
  20.     });
  21. }

Finally, for reading we need, for all objects to be returned, to reassemble the object from the two columns in the database, which can be easily implemented by ‘forEach’-ing the results as shown below.

  1. function read(query, user, request) {
  2.     request.execute({
  3.         success: function (results) {
  4.             results.forEach(function (item) {
  5.                 var dtoObject = { DateTimeUTC: item.Time, OffsetMinutes: item.Offset };
  6.                 delete item.Offset;
  7.                 item.Time = dtoObject;
  8.             });
  9.  
  10.             request.respond();
  11.         }
  12.     });
  13. }

That’s it. We don’t need to update the delete script since it is based on the object id.

This post is almost an update to the post about supporting complex types in mobile services using the new client API (based on JSON.NET), and hopefully it has shown that although some times the “impedance mismatch” between the runtime and the various client SDKs can cause problems, the Azure Mobile Services platform is extensible enough that we can, without a huge amount of code, work around the issues caused by the framework differences.

And as usual, please raise any concerns and suggestions in our MSDN forums or in the comments for those blogs.

Comments

  • Anonymous
    May 26, 2013
    Carlos, thanks for your post. I'll try using your second approach (that is a converter). Interestingly, I found a workaround that worked for INSERT, but not for selecting data back with table.Where(). Here's my (much simpler workaround if anyone needs it):Upon initializing Mobile Services, entirely removing DateTimeConverter that Mobile services provides inserts DateTimeOffset including timezone. I'll try your approach and will post the result.MobileService.SerializerSettings.DateParseHandling = Newtonsoft.Json.DateParseHandling.DateTimeOffset;               MobileService.SerializerSettings.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.RoundtripKind;               // remove date time converter               var conv = MobileService.SerializerSettings.Converters.Where(c => c is MobileServiceIsoDateTimeConverter).FirstOrDefault();               if (conv != null)               {                   MobileService.SerializerSettings.Converters.Remove(conv);               }
  • Anonymous
    August 15, 2013
    But what will happened if I manually created a table in SQL Azure with column of type DateTimeOffest?Is it possible to set it and read correctly form Mobile Service?Or do I need to use Stored Procedures?
  • Anonymous
    February 03, 2015
    Hey Carlos, is this post still valid or has something changed with how dates are handled in Azure?I just noticed my converter has been catching an exception on ReadJson for a very long time now, everything works fine though.. Any ideas?
  • Anonymous
    February 03, 2015
    I'm not in the azure mobile services team anymore, so I'm not sure, but I'd guess that in the node.js / JavaScript backend you'd still have this issue (after all, there's no concept of DateTimeOffset in JS), but if you're using the .NET backend it should just work, as that backend can deal with DateTimeOffset values natively.