CODEWITHSUNDEEP
sql-serverazureazure-cosmosdbazure-cosmosdb-sqlapi
RevolverOshawott
RevolverOshawott

Reputation: 101

Querying CosmosDB based on timestamps

I am working with a CosmosDB setup by one of my colleagues and connecting to it using a connection string. The database contains several JSON documents with the following schema:

{
    "period": "Raw",
    "source": "Traffic",
    "batchId": "ee737270-0b72-49b7-a2f1-201f642e9c81",
    "periodName": "Raw",
    "sourceName": "Traffic",
    "groupKey": "gc4151_a",
    "partitionKey": "traffic-gc4151_a-raw-raw",
    "time": "2021-08-05T23:55:10",
    "minute": 55,
    "hour": 23,
    "day": 05,
    "month": 08,
    "quarter": 3,
    "year": 2021,
    "minEventTime": "2021-08-05T23:55:09",
    "maxEventTime": "2021-08-05T23:55:11",
    "meta": {
        "siteId": "GC4151_A",
        "from": {
            "lat": "55.860894822588506",
            "long": "-4.284365958508686"
        },
        "to": {
            "lat": "55.86038667864348",
            "long": "-4.2826901232101795"
        }
    },
    "measurements": {
        "flow": [
            {
                "calculation": "Raw",
                "name": "flow",
                "calculationName": "Raw",
                "value": 0
            }
        ],
        "concentration": [
            {
                "calculation": "Raw",
                "name": "concentration",
                "calculationName": "Raw",
                "value": 0
            }
        ]
    },
    "added": "2021-08-05T12:21:32.000819Z",
    "updated": "2021-08-05T12:21:32.000819Z",
    "id": "d4346f50-543e-4c4d-82cf-835b480914c2",
    "_rid": "4RRTAIYVA1AIAAAAAAAAAA==",
    "_self": "dbs/4RRTAA==/colls/4RRTAIYVA1A=/docs/4RRTAIYVA1AIAAAAAAAAAA==/",
    "_etag": "\"1c0015a1-0000-1100-0000-5f3fbc4c0000\"",
    "_attachments": "attachments/",
    "_ts": 1598012492
}

I am trying to write a SQL query to select all the records that fall between the current date-time and one week earlier, so I can use these to perform future calculations.

I have attempted to use both of the following:

SELECT *
FROM c
WHERE c.time > date_sub(now(), interval 1 week);

and

SELECT *
FROM c
WHERE c.time >= DATE_ADD(CURDATE(), INTERVAL -7 DAY);

However, both of these return the following error:

Gateway Failed to Retrieve Query Plan: Message: {"errors":[{"severity":"Error","location":{"start":124,"end":125},"code":"SC1001","message":"Syntax error, incorrect syntax near '1'."}]}
ActivityId: 51c3b6f7-e760-4062-bd80-8cc9f8de5352, Microsoft.Azure.Documents.Common/2.14.0, Microsoft.Azure.Documents.Common/2.14.0

My question is what is the issue with my code, and how can I fix it?

Upvotes: 0

Views: 754

Answers (1)

ggordon
ggordon

Reputation: 10035

You may use DateTimeAdd and GetCurrentDateTime() to achieve this. Eg.

SELECT *
FROM c
WHERE c.time > DateTimeAdd("day",-7,GetCurrentDateTime() )

Let me know if this works for you.

Upvotes: 2

Related Questions