CODEWITHSUNDEEP
c#azure-cosmosdb
XamDev
XamDev

Reputation: 3647

DocumentDB Query to find created date

I want to retrieve the documents with only documentID and document Created DateTime details. I am able to get the DocumentID by following query but how can we get the Document created datetime.

select c.id from c

However, along with id I need to get the document created date also

Any help on this appreciated !

Upvotes: 0

Views: 4096

Answers (2)

Howard Edidin
Howard Edidin

Reputation: 63

The _ts is a Unix Timestamp value.

You need to convert it. The following C# sample will do it.

 private static DateTime UnixTimeStampToDateTime(long unixTimeStamp) 
{
   System.DateTime dtDateTime = new System.DateTime(1970, 1, 1, 0, 0, 0, 0);
   dtDateTime = dtDateTime.AddMilliseconds(unixTimeStamp);
   return dtDateTime;
}

Or you can create a UFD that you can use in your query, The following is one that will work 

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

You would use it as follows:

SELECT * FROM c WHERE udf.epochToDate(c._ts) == ....

Upvotes: 1

Jay Gong
Jay Gong

Reputation: 23782

I am able to get the DocumentID by following query but how can we get the Document created datetime.

From this official doc , the automatically generated properties of the document when it is created consists only of the following: _rid,_etag,_ts,_self,id.

_ts represents last updated timestamp of the resource, so if this document has not been updated, then this time is the createTime. You could query it via Select c.id,c._ts from c.

So, maybe you need to log createTime by yourself when you create documents so that you could query the createTime later.

Hope it helps you.

The id , _ts or other custom properties like DocumentReadingCreatedDate are all properties in the document which is not divided into system details or non-system details. Just some of the properties I mentioned above are the fields that cosmosdb will automatically generate for you.

So you just need to use sql : select c._ts from c to find the newest update time of this document. You could get a long type data such as 1520216339 ,then you could convert it into datetime format such as Sun Jan 18 22:16:56 CST 1970 in your program.

If your documents have been updated and you must get the create time of the document, you can't use _ts field. You could only use your custom field DocumentReadingCreatedDate.

Upvotes: 6

Related Questions