CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Amaros
Amaros

Reputation: 503

Mongo Date Aggregation Predefined Ranges

I am trying to perform an aggregation function on a date field in mongodb, and I'm interested in getting a count result in predefined date ranges.

For Example:

Is this even possible? Or do I have to perform a query which returns all the documents in a sorted order, and then divide them into buckets in code?

Thank you!

Upvotes: 1

Views: 807

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50426

This usually makes more sense to do with at least some limit on the date range selected, but the basic principle is a logical condition using the $cond operator to return a value where the codition matches. In this case it is passing a value to $sum for accumulation:

var now = new Date(),
    oneDay = ( 1000 * 60 * 60 * 24 ),
    24hours = new Date( now.valueOf() - oneDay ),
    48hours = new Date( 24hours.valueOf() - oneDay );

db.collection.aggregate([
    { "$group": {
        "_id": null,
        "24hours": {
            "$sum": {
                "$cond": [
                    { "$lt": [ 24hours, "$datefield" ] },
                    1,
                    0
                ]
            }
        },
        "48hours": {
            "$sum": {
                "$cond": [
                    "$and": [
                          { "$gt": [ 24hours, "$datefield" ] },
                          { "$lte": [ 48hours, "$datefield" ] }
                    ],
                    1,
                    0
                ]
            }
        },
        "gt48hours": { 
            "$sum": {
                "$cond": [
                    { "$gt": [ 48hours, "$datefield" ] },
                    1,
                    0
                ]
            }
        }
    }}
])

Here the $cond takes a logical expression to evaluate as it's first argument and either returns the "second" argument where true or the "third" where false. Basically either 1 or 0 depending on whether the condition was met.

Noting that the logical forms here for $gt and such here as used by the aggregation framework differ from the "query" operator of the same naming in that they take an array of two arguments, where the "first" argument is either greater or less than ( as selected ) the "second" argument. In this case, each test value against the actaul "datefield", representing the field in your document to compare to. These return true/false where the condition is met or where it is not.

This means that for the dates defined within the ranges selected for each condition, the count is either accumulated where the data meets the conditions or it is not. And that is how you get the different counts per each time period.

Upvotes: 1

Related Questions