CosmosDB query to return records created between test duration

Golla Anil Kumar 1 Reputation point Microsoft Employee

Hi TEam,

i have collection in cosmosdb. i would like write a SQL query from DataExplorer which will return all the records that are created in collection between time range. can you please help in this regard.


Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,533 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Anurag Sharma 17,586 Reputation points

    Hi anonymous user, welcome to Microsoft Q&A forum.

    There are couple of ways to work on date and time properties in Azure Cosmos DB:

    1) Using the already existing system defined element '_ts'. It specifies the last updated timestamp of the resource. The value is a timestamp.(Article)

    As this value is stored in timestamp we need to convert it in date field and then write the queries accordingly. Example below:

    SELECT *  
    FROM c where TimestampToDateTime(c._ts*1000) > "2021-03-19T06:44:26.0000000Z"  

    This query is going to return all the documents created/updated after the mentioned date in where clause.

    Read more on: New date and time system functions in Azure Cosmos DB

    2) Adding our own date field in the documents and querying based on it. Please note the date field here is in ISO 8601 UTC standard. Example below:

        "id": "3",  
        "firstname": "test2",  
        "lastname": "test",  
        "createddate": "2021-03-17T12:21:22.0000000Z"  
    SELECT * FROM root WHERE (root["createddate"] >= "2021-03-17T12:21:22.0000000Z")  

    This query is going to return all documents created on or after the mentioned date in where clause.

    Working with Dates in Azure Cosmos DB

    Please let us know if this helps or else we can discuss further as mentioned by @Navtej Singh Saini if you can provide few sample records.


    If answer helps, please mark it as 'Accept Answer'