How can I select the timescripts of my documents in Date readable format? I want to do something like this:
SELECT CAST(C._ts AS DATE) FROM C
Specific to Cosmos DB SQL Query only please.
Add a comment
|
2 Answers
Functions announced in October 2020 https://devblogs.microsoft.com/cosmosdb/new-date-and-time-system-functions/ can assist with this (taking care to convert seconds to milliseconds)
SELECT TimestampToDateTime(C._ts*1000) as DateTime FROM C
Comments
Please use UDF in Cosmos DB.
sample document:
udf:
function convertTime(unix_timestamp){
var date = new Date(unix_timestamp*1000);
var year = date.getFullYear();
var month = ("0"+(date.getMonth()+1)).substr(-2);
var day = ("0"+date.getDate()).substr(-2);
var hour = ("0"+date.getHours()).substr(-2);
var minutes = ("0"+date.getMinutes()).substr(-2);
var seconds = ("0"+date.getSeconds()).substr(-2);
return year+"-"+month+"-"+day+" "+hour+":"+minutes+":"+seconds;
}
SQL: SELECT udf.convertTime(c._ts) FROM c
Surely , you could refer to varied format in this case: Convert a Unix timestamp to time in JavaScript
Hope it helps you.
3 Comments
Valentin Petkov
Nice thanks, +1 is there a benefit to use the engine to convert or best will be to use JavaScript client side? I will go for client side...
Stephen Bailey
Need to pass the timestamp into the date call var date = new Date(unix_timestamp*1000);
jubi
SELECT udf.convertTime(c.CommandDatetimeEpoch) as CommandDate FROM c where c.DeviceId='asc' and c.CommandDate = '09/21/2019'... Can you suggest why this doesnt work?