Get previous month data in Azure Cosmos DB

Question

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())

Martin Liversage · Accepted Answer · 2019-12-10T22:34:43.370

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.

Thanks Martin will check this – arunraj770 Dec 13 '19 at 11:38 — arunraj770, Dec 13 '19 at 11:38

score 1 · Answer 2 · answered Aug 13 '21 at 04:31

1

This is now supported natively using DateTimeAdd

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

answered Aug 13 '21 at 04:31

Sam Barber

458
1
6
13

score 0 · Answer 3 · answered Oct 24 '21 at 13:19

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

Get previous month data in Azure Cosmos DB

3 Answers3