Storing DateTime in azure table storage

Question

I am using default example for storing a datetime value in table storage. One the field is calculated as follows

DateTime accMonth = new DateTime(DateTime.Now.Year, 
    DateTime.Now.Month, 1);

Usually above means a date with time being 00:00 .

However when I save this in table storage I see this time as

2018-04-01T18:30:00.000Z

Which looks strange to me! anyone knows why?

Solution 1

You're getting a different value because you're creating a date/time with local time zone (India is GMT+5:30). In Azure Storage, date/time values are saved as UTC.

What the SDK is doing is converting this date into UTC and then saving that date. That's why you're seeing a date/time value 5:30 hours before the date/time value you're setting.

Edm.DateTime under Property Types

To solve this problem, specify the date/time kind as UTC. Then the SDK will not do any conversion. So your code would be:

var accMonth = new DateTime(DateTime.Now.Year, 
            DateTime.Now.Month, 1, 0, 0, 0, DateTimeKind.Utc);

Solution 2

It seems that you want to format the DateTime as this: yyyy-MM-dd 00:00:00

If so, you could use the following code to achieve:

DateTime accMonth = new DateTime(DateTime.Now.Year,DateTime.Now.Month, 1);
string formatted = accMonth.ToLongTimeString();
double hour = DateTime.Now.Hour;
customer1.date = Convert.ToDateTime(formatted).AddHours(-hour+1);

The complete code is as below:

private static void Main()
{
    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("StorageConnectionString"));

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

    CloudTable table = tableClient.GetTableReference("people");

    CustomerEntity customer1 = new CustomerEntity("Harp1", "Walter1");

    DateTime accMonth = new DateTime(DateTime.Now.Year,DateTime.Now.Month, 1);
    string formatted = accMonth.ToLongTimeString();
    double hour = DateTime.Now.Hour;
    customer1.date = Convert.ToDateTime(formatted).AddHours(-hour+1);

    TableOperation insertOperation = TableOperation.Insert(customer1);

    table.Execute(insertOperation);
}

public class CustomerEntity : TableEntity
{
    public CustomerEntity(string lastName, string firstName)
    {
        this.PartitionKey = lastName;
        this.RowKey = firstName;
    }

    public CustomerEntity() { }

    public DateTime date { get; set; }
}

The screen shot is:

Comments

[Zohar Peled]

This is just a guess, but it might have something to do with your local time zone. That's the first place I'm looking in cases of datetime mismatch.

[Gaurav Mantri]

Where are you running this code? I am guessing from your local machine.

[Gaurav Mantri]

Also, are you sure it is displayed as 2018-04-01T18:30:00.000Z instead of 2018-03-31T18:30:00.000Z?

[Gaurav Mantri]

On behalf of @Mike Hjort Christensen - Can you show the code where you store accMonth?.

[Kzryzstof]

This definitely looks like an UTC issue. Make sure accMonth's Kind is DateTimeKind.Utc.

[Mandar Jogalekar]

yes it is displayed as 2018-03-31T18:30:00.000Z . does table storage store utc value by default removing the offset?

[Gaurav Mantri]

@Mike Hjort Christensen - Thanks for the edit. Isn't it weird that you can't comment, yet allowed to edit a post? :)

[Skadoosh]

Sorry for the late response, but does this mean that all dates in Table Storage are stored in UTC without any timezone so you always need to convert to local timezone?