CosmosDB query to return records created between test duration

Question

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.

Thanks,
Anil

Answer 1

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'