CODEWITHSUNDEEP
azure-cosmosdb
D. Sell
D. Sell

Reputation: 51

Is _ts on documentdb document indexed?

Is it better to include my own time-stamp (with milliseconds) for higher performance queries using BETWEEN look-ups based on time-stamp?

I understand from previous posts that it is represented as number of seconds since 1970. Since it is a core element I might guess it is not indexed.

Upvotes: 3

Views: 1861

Answers (1)

Krishnan Sundaram
Krishnan Sundaram

Reputation: 1401

_ts is range-indexed and max precision (-1) by default in DocumentDB, unless overridden explicitly by the user. Therefore you can execute range queries against it.

That said, if you need a more granular timestamp with milliseconds, it is best to use your own higher-precision attribute. For example, you could use a timestamp as a string in the ISO 8601 format, for example - "2016-08-29T21:48:38.334", and range-index that attribute. That will serve two purposes - enable the date and time to be human readable, and execute range queries efficiently against that attribute. An example query with this timestamp attribute (named as, say, "createdTs") would then be:

SELECT r.id FROM root r WHERE 
r.createdTs > "2016-08-29T21:48:38.334" and r.createdTs < "2016-08-29T21:50:00.000"

Upvotes: 5

Related Questions