skip to Main Content

I have an Azure CosmosDB table, and when I query it using Azure.Data.Tables C# SDK with a filter on Timestamp, it returns zero columns. When I remove the filter, it applies the remaining query successfully.

For example, the filter below is used:

Timestamp ge datetime'2022-11-04T22:24:14.851Z'

When I apply the identical filter on the Azure Portal, the query behaves as expected, and filters the rows based on the Timestamp property.

I am expecting rows to be returned because there are rows that satisfy this filter (they were added after this date).

2

Answers


  1. Chosen as BEST ANSWER

    Apparently this is a feature of the Azure.Data.Tables SDK:

    https://github.com/Azure/azure-sdk-for-net/issues/32468


  2. Using below query, you can filter results based on time stamp.

    "SELECT * FROM c WHERE (c["ShipDate"] >= "2014-09-30T23:14:25.7251173Z")"
    

    Steps I have followed to get data from cosmos db based on timestamp are,

    1. Created cosmos db in Azure.
    2. Created a C# console app and added below code,
    using Microsoft.Azure.Cosmos;
    using Microsoft.Azure.Cosmos.Linq;
    // </using_directives>
    
    // <endpoint_key> 
    // New instance of CosmosClient class using an endpoint and key string
    var Endpoint = "<cosmos db url>";
    var Key = "<yourkey>";
    CosmosClient cosmosClient = new CosmosClient(Endpoint, Key);// </endpoint_key>
    
    // <create_database>
    // New instance of Database class referencing the server-side database
    Database database = await cosmosClient.CreateDatabaseIfNotExistsAsync(
        id: "adventureworks"
    );
    // </create_database>
    
    // <create_container>
    // New instance of Container class referencing the server-side container
    Container container = await database.CreateContainerIfNotExistsAsync(
        id: "products",
        partitionKeyPath: "/category",
        throughput: 400
    );
    // </create_container>
    
    // <create_items> 
    // Create new items and add to container
    Product firstNewItem = new(
        id: "68719518388",
        category: "gear-surf-surfboards",
        name: "Sunnox Surfboard",
        quantity: 8,
        sale: true,
        ShipDate: DateTime.UtcNow.AddDays(-14)
    );
    
    Product secondNewitem = new(
        id: "68719518398",
        category: "gear-surf-surfboards",
        name: "Noosa Surfboard",
        quantity: 15,
        sale: false,
        ShipDate: DateTime.UtcNow.AddDays(-10)
    );
    
    await container.CreateItemAsync<Product>(
        item: firstNewItem,
        partitionKey: new PartitionKey("gear-surf-surfboards")
    );
    
    await container.CreateItemAsync<Product>(
        item: secondNewitem,
        partitionKey: new PartitionKey("gear-surf-surfboards")
    );
    // </create_items> 
    
    // <query_items_sql>
    // Query multiple items from container
    using FeedIterator<Product> feed = container.GetItemQueryIterator<Product>(
        queryText: "SELECT * FROM c WHERE (c["ShipDate"] >= "2014-09-30T23:14:25.7251173Z")"
    );
    
    // Iterate query result pages
    while (feed.HasMoreResults)
    {
        FeedResponse<Product> response = await feed.ReadNextAsync();
    
        // Iterate query results
        foreach (Product item in response)
        {
            Console.WriteLine($"Found item:t{item.name}");
        }
    }
    
    1. Added one more class with name Product.cs and added below code,
     class Product
    {
        public string id;
        public string category;
        public string name;
        public int quantity;
        public bool sale;
        public DateTime ShipDate;
    
        public Product(string id, string category, string name, int quantity, bool sale,DateTime ShipDate)
        {
            this.id = id;
            this.category = category;
            this.name = name;
            this.quantity = quantity;
            this.sale = sale;
            this.ShipDate = ShipDate;
    
        }   
    }
    
    1. Able to get rows based on timestamp filter condition from cosmos db.

    enter image description here
    Reference link

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search