question

anilgolla-2789 avatar image
0 Votes"
anilgolla-2789 asked ·

CosmosDB query to return records created between test duration

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

azure-cosmos-db
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@anilgolla-2789 - We are checking this. Meanwhile can you please provide sample record from this collection to check this further.

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered ·

Hi @anilgolla-2789, 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 @NavtejSaini-MSFT if you can provide few sample records.


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








· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@anilgolla-2789 Please let us know if you need any further help.

0 Votes 0 ·