Can We Code It?: Storing DateTimeOffset values in Azure Table Storage

I recently stumbled upon a weakness of Azure Table Storage: there is no native support for DateTimes with timezone information (a .NET DateTimeOffset datatype). If I’d read all the documentation thoroughly beforehand I’d have known this (see http://msdn.microsoft.com/library/azure/jj553018.aspx) but like most of us I didn’t read all the documentation… Embarrassed smile

And, to be fair, I did have some reason for thinking that Table Storage would support DateTimeOffset values. Below is an example of a very basic ITableEntity class that can be read from/written to Table Storage using the Azure SDK methods in the
Microsoft.WindowsAzure.Storage.Table namespace:

public class ExampleEntity : ITableEntity
{
    public void ReadEntity(IDictionary<string, EntityProperty> properties, OperationContext operationContext)
    {
        ExampleDateWithTimeZone = properties.GetDateTimeOffset("ExampleDateWithTimeZone").GetValueOrDefault();
    }
 
    public IDictionary<string, EntityProperty> WriteEntity(OperationContext operationContext)
    {
        Dictionary<string, EntityProperty> properties = new Dictionary<string, EntityProperty>();
        properties.Add("ExampleDateWithTimeZone", EntityProperty.GeneratePropertyForDateTimeOffset(ExampleDateWithTimeZone));
        return properties;
    }
 
    public DateTimeOffset ExampleDateWithTimeZone { get; set;}
 
    public string ETag { get; set;}
 
    public string PartitionKey{ get; set;}
 
    public string RowKey{ get; set;}
 
    public DateTimeOffset Timestamp{ get; set;}
}

Look at lines 5 and 11: there are GetDateTimeOffset and GeneratePropertyForDateTimeOffset extension methods to read and write DateTimeOffset values, but no equivalent methods that read and write DateTime values. I saw this and thought “Great! It only supports dates with timezone information, which makes sense when the datacentre will normally be in a different timezone from the users”.

But unfortunately not…

When you do use those tempting helper methods for DateTimeOffSets what happens is:

When you write the value, Azure converts it to GMT, so

24/04/2014 14:31 +02:00

becomes

24/04/2014 12:31 +00:00
When you read it back you get the GMT value.

I didn’t notice this for a while because until the clocks changed in spring all my work on this project had taken place in GMT (one of the perils of developing in the U.K; you’re generally pretty careful about date formats, but not so careful about timezones). I did have some unit tests that used DateTimeOffset values with timezones but they were passing because according to DateTimeOffset.Equals():

24/04/2014 14:31 +02:00 == 24/04/2014 12:31 +00:00

(I can’t quite decide whether that’s a good thing or not)

So what did I do?

I still needed to store DateTimeOffset values in Azure Table Storage, so I converted them to strings. Not that revolutionary, but works quite nicely. In case I ever needed to sort them as DateTimeOffsets I used the format string “yyyyMMddHHmmssfffffffzzz”, so

01 May 2014 23:11:19 +01:00

becomes:

201405012312508966200+01:00

It turned out to be a fairly easy change to introduce because all my ReadEntity and WriteEntity implementations made heavy use of the same set of Extension Methods that look roughly like this:

public static void AddPropertyIfNotNull(this Dictionary<string, EntityProperty> properties, string propertyName, int? propertyValue)
{
    if (propertyValue != null)
    {
        properties.Add(propertyName, EntityProperty.GeneratePropertyForInt(propertyValue));
    }
}
 
public static int? GetInt32(this IDictionary<string, EntityProperty> properties, string propertyName)
{
    return getValue(properties, propertyName, ep => ep.Int32Value, () => null);
} 

private static T getValue<T>(IDictionary<string, EntityProperty> properties, string propertyName, Func<EntityProperty, T> valueAccessor, Func<T> nullValue)
{
    if (properties.ContainsKey(propertyName))
    {
        return valueAccessor(properties[propertyName]);
    }
    else
    {
        return nullValue();
    }
}

I have AddPropertyIfNotNull and GetXXX methods for every primitive type that I need, including DateTimeOffset. So all I had to do was change the extension methods for DateTimeOffset to look like this:

private const string DATETIMEOFFSET_FORMAT = "yyyyMMddHHmmssfffffffzzz";

public static DateTimeOffset? GetDateTimeOffset(this IDictionary<string, EntityProperty> properties, string propertyName)
{
    string valueAsString = getValue(properties, propertyName, ep => ep.StringValue, () => null);
    if (valueAsString == null)
    {
        return null;
    }
    else
    {
        return DateTimeOffset.ParseExact(valueAsString, DATETIMEOFFSET_FORMAT,  CultureInfo.DefaultThreadCurrentUICulture);
    }
}
 
public static void AddPropertyIfNotNull(this Dictionary<string, EntityProperty> properties, string propertyName, DateTimeOffset? propertyValue)
{
    if (propertyValue != null)
    {
        properties.Add(propertyName,  EntityProperty.GeneratePropertyForString(propertyValue.Value.ToString(DATETIMEOFFSET_FORMAT)));
    }
}

And the rest of the application carried on working as normal.

And if Azure Table Storage starts supporting DateTimeOffset natively before I go live all I have to do is switch it back…

Can We Code It?

Thursday, 1 May 2014

Storing DateTimeOffset values in Azure Table Storage

3 comments: