Search code examples
azureazure-cosmosdbazure-cosmosdb-sqlapi

How do I query the last 30 days on CosmosDB?

I'm looking to retrieve records from the last 30 full days.

How would I be able to amend the partitionID below, so that it does not bring back any records past the 30 day mark?

SELECT * FROM c WHERE c.partitionID LIKE '%-2021-12-17%'

Solution

  • If you have a column where you have the datetime in the ISO 8601 format, e.g.:

    {
    "id": "test",
    "partitionKey": "a",
    "date": "2021-11-01T20:36:17.1234567Z"
}

    You can query using the Date and time functions:

    SELECT * FROM c
WHERE DateTimeDiff("day", c.date, GetCurrentDateTime()) <= 30

    If you wanted all the items within a certain time period (here for example all items in December 2021):

    SELECT * FROM c
WHERE c.date >= '2021-12-01T00:00:00.0000000Z' AND c.date < '2021-12-31T00:00:00.0000000Z'