Unable to query by Timestamp in CosmosDB with Table API

Question

I'm creating a query to include Cosmos entities from the last 30 days:

var filter = TableQuery.GenerateFilterConditionForDate(
                "Timestamp", 
                QueryComparisons.GreaterThanOrEqual,
                DateTimeOffset.Now.Date.AddDays(-30));

Next I create a query using this filter:

var query = new TableQuery<ResponseEntity>().Where(filter);

Next I execute the query:

var result = await table.ExecuteQuerySegmentedAsync(query, null);

However, for some reason, the result always contains zero (0) hits.

If I execute the query without any filter...

var query = new TableQuery<ResponseEntity>();

...I do get all entities.

Looking at the generated filter string, it looks OK to me (and identical to the one in the Azure portal when using the query builder for Cosmos):

Timestamp ge datetime'2018-09-15T22:00:00.0000000Z'

Is there any limitation on querying based on Timestamp?

Edit: Tried switching to the new Microsoft.Azure.Cosmos.Table NuGet package (currently in preview, version 0.9.1), but I'm still not getting any results when filtering by Timestamp.

Answer 1

Please refer to my working code.

code:

using Microsoft.Azure.CosmosDB.Table;
using Microsoft.Azure.Storage;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JayGongCosmosTable
{
    class Program
    {
        static void Main(string[] args)
        {
            TableQuerySegment <ResponseEntity>  resultE=  QueryTableAsync("test").Result;
            foreach(ResponseEntity re in resultE)
            {                    
                Console.WriteLine("Timestamp:   "+re.Timestamp);
                Console.WriteLine("------------------------------------------");
            }
            Console.WriteLine("execute done");
            Console.ReadLine();
        }

        public static async Task<TableQuerySegment<ResponseEntity>> QueryTableAsync(string tableName)
        {
            CloudStorageAccount storageAccount = CreateStorageAccountFromConnectionString("***");
            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
            CloudTable table = tableClient.GetTableReference(tableName);

            var filter = TableQuery.GenerateFilterConditionForDate(
                "Timestamp",
                QueryComparisons.GreaterThanOrEqual,
                //QueryComparisons.LessThanOrEqual,
                DateTimeOffset.Now.AddDays(-10).Date);

            Console.WriteLine(filter);

            var query = new TableQuery<ResponseEntity>().Where(filter);

            var result = await table.ExecuteQuerySegmentedAsync(query, null);

            return result;

        }
    }


    class ResponseEntity : TableEntity
    {    
        public string Name { get; set; }

        public DateTimeOffset logtime { get; set; }

    }
}

My data list as below without filter:

If I used Timestamp as filter, it works:

Another thing I'd like to mention is, please avoid timestamp interval query if possible. Such a query will result in a whole table scan in server side. If timestamp interval query is needed usually in your scenario, please consider choosing timestamp as your partition key or row key to optimize the query performance.

---

Just for summary, finally , the solution is uninstalling WindowsAzure.Storage and switch using statements to use types from Microsoft.Azure.CosmosDB.Table instead.