CosmosDB, SQL query, filter timestamp field with unixtimestamp

1
votes

I have a cosmosdb that I'm querying with SQL in which I would like to filter on a Timestamp field that is in UnixTimestamp format. Here is the query that I have so far:

SELECT 
root._id as hpid
,root.FirstConnected["$date"] as FirstConnected
,root.LastUpdated["$date"] as LastUpdated
,var._id as varid
,var.Values[0].Timestamp["$date"] as TimeStamp
,var.Values[0]["Value"] as Val
FROM root 
JOIN var IN root.Variables
WHERE 
var._id IN (99998,99999) AND 
var.Values[0].Timestamp["$date"] >= 1523270312001

Here is the result:

[
{
    "hpid": 21032,
    "FirstConnected": 1522835868346,
    "LastUpdated": 1523360279908,
    "varid": 99998,
    "TimeStamp": 1523270312001,
    "Val": 8888
}
]

Is currently a good way to be able to dynamically filter on the TimeStamp value which is in UnixTimeStamp? Lets say I want to filter last 5 days from today?

Any help appreciated.

2
azure-cosmosdb

2 Answers

1
votes

To dynamically calculate the timestamp, you can create a user defined function (udf) in Cosmos DB, and call it in your query.

For example, you can define a udf as follows: 

function getDateTarget(){
    var date = new Date();
    date.setDate(date.getDate() - 5)
    return Math.floor(date.getTime() / 1000);
}

And use it in your query: SELECT * FROM c WHERE c._ts > udf.getDateTarget()

0
votes

Yes the above way should work

var fiveDaysAgo = new Date();
fiveDaysAgo.setDate(d.getDate() - 5);
var threshold = twoDaysAgo.getTime() / 1000;

SELECT 
root._id as hpid
,root.FirstConnected["$date"] as FirstConnected
,root.LastUpdated["$date"] as LastUpdated
,var._id as varid
,var.Values[0].Timestamp["$date"] as TimeStamp
,var.Values[0]["Value"] as Val
FROM root 
JOIN var IN root.Variables
WHERE 
var._id IN (99998,99999) AND 
var.Values[0].Timestamp["$date"] >= @threshold