Azure table storage querying partitionkey

Question

I am using Azure table storage to retrieve data though timestamp filter. I see the execution is very slow as timestamp is not a partition key or row key. I researched on stackoverflow and found that time stamp should be converted to ticks and stored in to Partition key. I did the same and while inserting data I took the below string and inserted tick string to partition key.

string currentDateTimeTick = ConvertDateTimeToTicks(DateTime.Now.ToUniversalTime()).ToString();

public static long ConvertDateTimeToTicks(DateTime dtInput)
{
    long ticks = 0;
    ticks = dtInput.Ticks;
    return ticks;
}

This is fine till here. But When I am trying to retrieve last 5 days data, I am unable to query the tick against partition key. I am trying to get last 5 days data. What was my mistake in the below code?

int days = 5;
TableQuery<MyEntity> query = new TableQuery<MyEntity>()
.Where(TableQuery.GenerateFilterConditionForDate("PartitionKey", QueryComparisons.GreaterThanOrEqual, "0"+DateTimeOffset.Now.AddDays(days).Date.Ticks));

Answer 1

Are you sure you want to use ticks as a partition key? This means that every measureable 100 ns instant becomes it's own partition. With time based data you can use the partition key to specify an interval like every hour, minute or even second and then a row key with the actual timestamp.

That problem aside let me show you how to do the query. First let me comment on how you generate the partition key. I suggest you do it like this:

var partitionKey = DateTime.UtcNow.Ticks.ToString("D18");

Don't use DateTime.Now.ToUniversalTime() to get the current UTC time. It will internally use DateTime.UtcNow, then convert it to the local time zone and ToUniversalTime() will convert back to UTC which is just wasteful (and more time consuming than you may think).

And your ConvertDateTimeToTicks() method serves no other purpose than to get the Ticks property so it is just making your code more complex without adding any value.

Here is how to perform the query:

var days = 5;
var partitionKey = DateTime.UtcNow.AddDays(-days).Ticks.ToString("D18")
var query = new TableQuery<MyEntity>().Where(
  TableQuery.GenerateFilterCondition(
    "PartitionKey",
    QueryComparisons.GreaterThanOrEqual,
    partitionKey
  )
);

The partition key is formatted as an 18 characters string allowing you to use a straightforward comparison.

I suggest that you move the code to generate the partition key (and row key) into a function to make sure that the keys are generated the same way throughout your code.

The reason 18 characters are used is because the Ticks value of a DateTime today as well as many thousands of years in the future uses 18 decimal digits. If you decide to base your partition key on hours, minutes or seconds instead of 100 ns ticks then you can shorten the length of the partition key accordingly.