How _ts change in DocumentDB

Question

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).

Anaemic

Answer 1

_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.

Petulah

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? – Supererogate

Practically, speaking you should never see a lower _ts value for a given partition... but this is something that is not guaranteed. – Petulah

Here's a devblog source: devblogs.microsoft.com/cosmosdb/… – Straightaway

Answer 2

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 

Alabama

If anybody is wondering why you multiply _ts by 1000 in that query: _ts is in seconds in CosmosDB, but TimestampToDateTime works in milliseconds. – Straightaway

Thanks for that. Really good to find that function. – Knackwurst