CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Ms13
Ms13

Reputation: 151

How to get all the dates between two dates in mongodb aggregation?

If start_date is '2019-08-10' and end_date is '2019-09-15' then i want the o/p as 

'2019-08-10'
'2019-08-11'
'2019-08-12'
'2019-08-13'
'2019-08-14'
............
............
............
'2019-09-14'
'2019-09-15'

I have tried using the $range function but when the date different is huge (more than 100 days) it's throwing an error "$range requires an ending value that can be represented as a 32-bit integer, found value: 1.57248e+10"

db.test.aggregate([
    { $addFields: { days_in_millis: { $add: [{ $subtract: ["$end_date", "$start_date"] }, 86400000] } } },
    { $project: { end_date: 1, start_date: 1, millis_range: { $range: [0, "$days_in_millis", 86400000] } } },
    {
        $project: {
            dates_in_between_inclusive: {
                $map: {
                    input: "$millis_range",
                    as: "millis_count",
                    in: { $add: ["$start_date", "$$millis_count"] }
                }
            }
        }
    },
    { $unwind: "$dates_in_between_inclusive" }
])

Upvotes: 0

Views: 1588

Answers (1)

Buzz Moschetti
Buzz Moschetti

Reputation: 7558

This works. It is basically the same thing you are doing but works because the heavy lifting is done by the $multiply function instead of getting caught up in $range? I tested it with an end date of 20300101 so really big intervals work OK as well.

var d = {
    "start_date": new ISODate("20190810"),
    "end_date": new ISODate("20190915")
};

c = db.foo.aggregate([

{$addFields: {days_diff: {$divide: [{$subtract: ["$end_date", "$start_date"]}, 86400000.0 ]} }}

,{$project: {dates_in_between_inclusive: {
            $map: {
                input: {$range: [0, {$add:["$days_diff",1]}] }, // +1 for inclusive
                as: "dd",
                in: {$add: ["$start_date", {$multiply:["$$dd", 86400000]}]}
            }
        }}},

{$unwind: "$dates_in_between_inclusive"}
                      ]);

yields

{
    "_id" : ObjectId("5d6db092a09f8fe062710b63"),
    "dates_in_between_inclusive" : ISODate("2019-08-10T00:00:00Z")
}
{
    "_id" : ObjectId("5d6db092a09f8fe062710b63"),
    "dates_in_between_inclusive" : ISODate("2019-08-11T00:00:00Z")
}
...

Upvotes: 4

Related Questions