22

I have a question regarding the _ts field within the documentdb. How is it determined? My understanding is that when a document is added/altered it gets a new _ts. Are there any chance that two documents have the same _ts? If so, does it happen only if those documents are added at the exact time (in terms of milli second).

HHH
  • 6,085
  • 20
  • 92
  • 164

2 Answers2

28

_ts is a system property denoting when a document was last updated (e.g. create or replace).

_ts is represented as a POSIX or epoch time value. In other words, its the number of seconds (not milliseconds) that have elapsed since 00:00:00 (UTC), 1 January 1970.

It is possible to have the same _ts value across multiple documents when there are multiple documents written/updated during the same second.

Andrew Liu
  • 8,045
  • 38
  • 47
  • This means that you could see another document with the same _ts. But, is it at least guaranteed (with session consistency) that you'll never see a lower _ts? If so, then an incremental update could use "greater than or equal to" and deal with duplicates from the last read (idempotency). Or is that not even guaranteed? – Larry Maccherone Apr 19 '16 at 08:13
  • 1
    Practically, speaking you should never see a lower `_ts` value for a given partition... but this is something that is not guaranteed. – Andrew Liu Apr 30 '16 at 00:18
  • Here's a devblog source: https://devblogs.microsoft.com/cosmosdb/new-date-and-time-system-functions/ – Phil Feb 25 '23 at 00:57
1

As Andrew Liu has mentioned the _ts property shows the date and time which the document was last updated.

the following function can be used to get the time in _ts in a human readable format TimestampToDateTime(c._ts*1000).

Example :

SELECT c.FirstName , TimestampToDateTime(c._ts*1000) AS 'time' FROM Person c
dinith jayabodhi
  • 531
  • 2
  • 8
  • 19
  • 2
    If anybody is wondering why you multiply `_ts` by 1000 in that query: `_ts` is in **seconds** in CosmosDB, but [TimestampToDateTime](https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/timestamptodatetime) works in **milliseconds**. – Phil Feb 25 '23 at 01:00