Azure's CosmosDB - Deleting all documents in a date range

Question

I want to delete all documents created between the dates 2017-08-18 and 2017-08-22. I was told that all documents have CosmosDB's own timestamp under the field '_ts'. I tried using the document explorer to filter the documents using this query c._ts > "2017-08-18" but it does not return any document. I also tried c._ts > 2017-08-18 without the quotes but that returns all documents, even those before that date, just as if there was no query at all. Looking at the documents, I do not even see a '_ts' field. Is there any way I can delete those documents? Thanks.

Answer 1

_ts is Azure Cosmos DB's internal Timestamp property

The _ts field is a unix-style epoch timestamp representing the date and time. The _ts field is updated every time a document is modified.

If you want to query in a date range, we can do that easliy with udf function, more details please refer to another SO thread.

SELECT * FROM c where udf.udfname(c._ts)>'2017-08-18'

udf function

function epochToDate (ts) { 
   return new Date(ts*1000);
}

Answer 2

According to https://msdn.microsoft.com/en-us/library/azure/microsoft.azure.documents.resource.timestamp.aspx#P:Microsoft.Azure.Documents.Resource.Timestamp

_ts gets the last modified timestamp associated with the resource from the Azure DocumentDB database service.

[JsonPropertyAttribute(PropertyName = "_ts")] [JsonConverterAttribute(typeof(UnixDateTimeConverter))] public virtual DateTime Timestamp { get; internal set; }

Its is represented as a POSIX or epoch time value. Its the number of seconds that have elapsed since 00:00:00 (UTC), 1 January 1970.