Importing DateTime from Azure Table to R and Matlab

Processing time data from Azure Table in R and Matlab can be quite tricky. First DateTime gets converted to UTC when inserted into Azure Tables [msdn]. Then Powershell’s Export-CSV takes those dates and format them using the current locale (say “11/26/2014 5:31:16 AM” in North America). This is not what R and Matlab expect and conversions are needed to use those dates.

DateTime from Azure Table to R

R needs to be given the default .NET date format to parse the date string:

> dateFromAzureTable <- "11/26/2014 5:31:16 AM"
> dateUtc <- as.POSIXct(dateFromAzureTable, tz="GMT", "%m/%d/%Y %I:%M:%S %p")
> dateUtc
[1] "2014-11-26 05:31:16 GMT"

The date can then be converted from UTC to local time:

> dateLocal <- format(dateUtc, tz="", usetz=T)
> dateLocal
[1] "2014-11-25 21:31:16 PST"

DateTime from Azure Table to Matlab

Date parsing is similar in Matlab, using datenum():

>> dateFromAzureTable = '11/26/2014 5:31:16 AM'
>> dateUtc = datenum(dateFromAzureTable, 'mm/dd/yyyy HH:MM:SS AM')
>> datestr(dateUtc)
ans =
26-Nov-2014 05:31:16

The problem with Matlab is that until R2014a it did not support the notion of time zones [mathworks]. For older versions, Matlab’s interop with Java gives access to the TimeZone class which provides the offset between UTC and local time:

>> offsetUtcToLocal = java.util.TimeZone.getDefault().getRawOffset() / (1000 * 60 * 60 * 24)
>> dateLocal = dateUtc + offsetUtcToLocal
>> datestr(dateLocal)
ans =
25-Nov-2014 21:31:16

Further reading

This MSDN article has an enlightening list of common gotchas around processing dates.