required query for Cosmosdb timestamp calculation

Question

My cosmosdb is updated daily with some data , which looks like

{
    "Price": {
        "For": "9070.040",
        "From": "700.990"
    },
    "ArticleNumber": "71151004",
    "ArticleNumberPartitionKey": "7115",
    "ForStatus": "ACTIVE",
    "id": "71151004",
    "_rid": "ky1XAMpBiEoDAAAAAAAAAA==",
    "_self": "dbs/ky1XAA==/colls/ky1XAMpBiEo=/docs/ky1XAMpBiEoDAAAAAAAAAA==/",
    "_etag": "\"1200f241-0000-0d00-0000-60c8dad00000\"",
    "_attachments": "attachments/",
    "_ts": 1623775952
}

My logic is setup with recurrance of 24 hrs. My issue is I am unable to get the data for last 24 hrs if any new data gets added in the cosmosdb. I would like to use "_ts" to get the data for last 24 horurs only. Any idea how to do that?

tried with this but not getting the required result.

Int32 unixTimestamp = (Int32)(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;

Have you tried something that isn't working? _ts is a Unix timestamp and 24 hours ago is easily calculated in most languages — Noah Stahl

Noah Stahl Noah Stahl · Accepted Answer · 2021-06-23T18:56:16

In .NET the following will get the unix time 24 hours ago:

long hoursAgo24 = DateTimeOffset.UtcNow.AddHours(-24).ToUnixTimeSeconds();

With that value you should be able to run a query like:

SELECT * FROM c
WHERE c._ts > @hoursAgo24

required query for Cosmosdb timestamp calculation

1 Answers