How to query objects in Azure Cosmos DB with datetime field

Question

We have a requirement to filter the objects of Azure Cosmos DB using datetime field. The structure of the object is as follows.

{
    "nodeId": 3,
    "nodeName": "HAVC",
    "assetId": 2,
    "assetName": "Chiller_02",
    "assetMeasurementId": 22,
    "assetMeasurementName": "CHL01_WATER_ENTER_TEMP",
    "streamId": 0,
    "streamName": null,
    "deviceId": 4,
    "deviceTagId": 48,
    "rawTelemetryData": 49,
    "observationPropertyId": 1,
    "isActive": false,
    "enqueueDatetime": "2019-05-31T07:17:45",
    "receivedDatetime": "2019-06-03T18:51:16.6684284",
    "id": "1e9fe261-5e73-4a64-8921-817a6c601ea0",
    "_rid": "PfsxALhViWYWAAAAAAAAAA==",
    "_self": "dbs/PfsxAA==/colls/PfsxALhViWY=/docs/PfsxALhViWYWAAAAAAAAAA==/",
    "_etag": "\"0000f80f-0000-1800-0000-5cf51e930000\"",
    "_attachments": "attachments/",
    "_ts": 1559568019
}

In this, we need to query the data using 'receivedDatetime' field and we need data between '18:51' to '18:57'

In C#, we use a syntax similar to LINQ

var queryable = client.CreateDocumentQuery<TestDto>(
        UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
        new { EnableCrossPartitionQuery = true })
    .Where(x => x.receivedDatetime >= DateTime.Parse(startDate))
    .Where(x => x.receivedDatetime <= DateTime.Parse(endDate))
    .OrderBy(x => x.receivedDatetime)
    .AsDocumentQuery();

But we were unable to receive the data as expected. Appreciate if you can kindly provide some support

Agrawal Shraddha · Accepted Answer · 2019-07-04 05:51:38Z

1

You can check this -DateTime, the Epoch and DocumentDb You have to store receivedDatetime in ISO-8601 date/time format

answered Jul 4, 2019 at 5:51

Agrawal Shraddha

7741 gold badge7 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sajeetharan · Accepted Answer · 2019-07-04 06:24:31Z

0

To do efficient range queries on a numeric field in Cosmosdb you have to define a Range Index on the path containing your numeric field when you create the Container.

CosmosDB does not support querying DateTime. You can have a look on the blog here to understand more

answered Jul 4, 2019 at 6:24

Sajeetharan

223k65 gold badges370 silver badges409 bronze badges

1 Comment

jbiversen Over a year ago

Looks like that has been fixed according to this page learn.microsoft.com/da-dk/azure/cosmos-db/sql/…

Collectives™ on Stack Overflow

How to query objects in Azure Cosmos DB with datetime field

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related