Query performance with date time field in Cosmos DB

Question

I have a date time field and as recommended in the Microsoft documentation, I am storing it as a String in UTC format. This field is also one of the partition keys for the container.

I want to perform range queries on this field. Could you please let me know out of the below queries, which one will be faster and why:-

1. select * from c where c.creationDate between '2023-03-01' and '2023-03-25' or
2. select * from c where c.creationDate between '2023-03-01T00:00:00' and '2023-03-25T23:59:59'

Answer 1

Whether the datetime is part of a hierarchical partition key or a synthetic partition key, this query will not scale because Cosmos uses hash-based partitioning. You cannot apply a range predicate to scope to a subset of partitions. This means that either query above will fan-out across all partitions, which is something you need to avoid if you want to scale.

While partitioning is hash-based, the indexes are range-based. Datetime needs to be stored and queried in ISO 8061 format. From the docs.

Range queries with DateTime strings as filters are only supported if the DateTime strings are all in UTC. In Azure Cosmos DB, the GetCurrentDateTime system function will return the current UTC date and time ISO 8601 string value in the format: yyyy-MM-ddTHH:mm:ss.fffffffZ.