$subtract "Date and current date"

[Andrés]

Hello,

I have a collection "temp" that contains the following documents (for example):

{ "_id" : 1, "user" : "A", "StartDate": IsoDate ("2014-03-18T05:47:33Z")}

{ "_id" : 2, "user" : "B", "StartDate": IsoDate ("2012-01-11T03:34:54Z")}

{ "_id" : 3, "user" : "C", "StartDate": IsoDate ("2014-04-18T05:47:33Z")}

I want to know how to calculate how many days have passed since "StartDate" until Today.

I tried something... using $subtract and $currentDate, but I have not gotten results.

Any suggestions?

Thank you!

[Wan Bachtiar]

Hi Andres,

Given your record examples, in order to query the number of days passed since ‘StartDate’ you can use $subtract operator in aggregation pipeline.

This example substracts current date with StartDate, returns the difference in milliseconds and days.

db.temp.aggregate([
    {$project: { 
        "user": 1, 
        "diff_msecs": {
            /* Calculate date difference in milliseconds */
            $subtract:[
                new ISODate(), 
                "$StartDate"
                ]
            }
        }
    }, 
    {$project: {
        "user": 1, 
        "diff_msecs": 1, 
        "diff_days": {
            /* Convert the previous result in milliseconds to days */
            $divide: [
                "$diff_msecs", 
                1000 * 60 * 60 * 24
                ]
            }
        }
    }]
)

See https://docs.mongodb.org/manual/reference/operator/aggregation/subtract/#subtract-two-dates for more examples on date substraction.

Regards,

Wan.

​

[Rohit Jain]

Hi Wan,

  It can be done in one shot like this 

db.temp.aggregate([{$project:{_id:1,user:1,diff_days:{$divide:[{$subtract:[new ISODate(),"$StartDate"]},1000 * 60 * 60 * 24]}}}])

Do you think this approach have some performance issue ?

Thanks & Regards,

Rohit

[Wan Bachtiar]

Hi Rohit,

The intention is to provide an example to clearly show steps for calculating date difference - that the calculation is done in milliseconds.

A single aggregation step/calculation is possibly more efficient, but you would have to try with your own use case to see if this makes a measurable difference.

One issue with the current example is that it would be calculating the date difference for all documents in your collection. I assume you would probably add an initial $match stage to limit the documents processed.

Another approach you could consider is performing this calculation in your application code rather than on the database server. 

If you have more application servers than database servers this might distribute the workload better.

Kind Regards,

Wan.

PS: I’ve found the documentation on Aggregation Pipeline Optimization is insightful in general, hope this can also benefit others.

​

​

[Andrés]

Thank you Rohit!

I tried it, but shows me this error:

bash: syntax error near unexpected token `('

Any suggestions?

Thx!!!

[Andrés]

Ey Wan! Thank you so much! It worked ;).