CODEWITHSUNDEEP
sqlazure-cosmosdb
arunraj770
arunraj770

Reputation: 827

Get previous month data in Azure Cosmos DB

Hi I am trying to get previous 4 month records from azure cosmos db.

SELECT * FROM c where c.userid = '100' and c.Date < GetCurrentDateTime()

This gives all the records from current datetime

I have one other date field called preDate. Now i want to get the records which are less than 4 months from this preDate something like:

 SELECT * FROM c where c.userid = '100' and c.Date > (c.preDate - 4 months)

is there anything equivalent like DATEADD() for CosmosDB some thing a query like

SELECT * FROM TABLE_NAME WHERE Date_Column >= DATEADD(MONTH, -4, GETDATE())

Upvotes: 4

Views: 3744

Answers (3)

Hung Vu
Hung Vu

Reputation: 5914

Don't need to create a user-defined function, just use the DateTime functions provided by the CosmosDB itself.

SELECT * FROM c where c.userId = "292789" and c.Date < GetCurrentDateTime() and c.Date > DateTimeAdd("dd", -1, GetCurrentDateTime()) order by c.Date desc

Upvotes: 0

Sam Barber
Sam Barber

Reputation: 568

This is now supported natively using DateTimeAdd

SELECT * FROM c 
WHERE c.userid = '100' and 
c.Date > DateTimeAdd(‘month’, -4, GetCurrentDateTime())

Upvotes: 2

Martin Liversage
Martin Liversage

Reputation: 106826

You can create a user defined function. I created this directly in the explorer:

function AddMonths(dateTime, months) {
    var date = new Date(dateTime);
    var day = date.getUTCDate();
    date.setUTCMonth(date.getUTCMonth() + months, 1);
    var month = date.getUTCMonth();
    date.setUTCDate(day);
    if (date.getUTCMonth() !== month)
        date.setUTCDate(0);
    return date.toISOString();
}

The UDF is identified by ID (which doesn't have to match the function name in JavaScript). In this case I named it AddMonths.

The UDF can the be used in queries:

SELECT * FROM c WHERE c.userid = '100' and c.Date > udf.AddMonths(c.preDate, -4)

Notice that offsetting a date by months is non-trivial because you have to take into account that months can have 28-31 days. I took inspiration from aMarCruz' answer here on Stack Overflow but you may have to consider if that is the right way for you to handle this.

If your dates were stored as UNIX timestamps (e.g. numbers) you could perform arithmetic directly in Cosmos SQL but to decide how many seconds to subtract for 4 months would still require a UDF so it's not really a better option.

One thing I noticed is that when storing C# DateTime values in Cosmos they are specified with 100 nanosecond precision. However, in JavaScript (using toISOString) the precision is 1 microsecond. This could potentially lead to confusing errors in certain corner cases so just be aware of this.

Upvotes: 3

Related Questions