CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
MrBinWin
MrBinWin

Reputation: 1319

Mongodb count results grouped by date range

I have documents like these:

{name: 'doc1', date: "2015-01-01T02:00:12+01:00"},
{name: 'doc2', date: "2015-01-01T03:02:12+01:00"},
{name: 'doc3', date: "2015-01-01T02:17:55+01:00"}

Is it possible to count them by time-intervals (for example: 15 minutes) and get result like this:

{startDate: "2015-01-01T02:00:12+01:00", count: 15},
{startDate: "2015-01-01T02:15:12+01:00", count: 11},
{startDate: "2015-01-01T02:30:12+01:00", count: 21},
...`

Upvotes: 2

Views: 3181

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

You can't get an "actual" date object returned this way but you can get a timestamp value which can be used to construct a date object. I'ts just a simple matter of date math:

db.collection.aggregate([
    { "$group": 
        "_id": {
            "$subtract": [
                { "$subtract": [ "$date", new Date("1970-01-01") ] },
                { "$mod": [
                    { "$subtract": [ "$date", new Date("1970-01-01") ] },
                    1000 * 60 * 15
                ]}
            ]
        },
        "count": { "$sum": 1 }
    }}
])

Subtracting a "date object" with the epoch date will result in the current timestamp value as a number. The basic math is the difference from the modulo at a 15 minute interval ( 1000 milis * 60 secs * 15 minutes ).

If you prefer there are actually Date Aggregation Operators which can split up the date as well. Same case is that these are numbers and not a date, but you can re-construct a date object from the values there.

db.collection.aggregation([
    { "$group": {
        "_id": {
            "year": { "$year": "$date" },
            "month": { "$month": "$date" },
            "dayOfMonth": { "$dayOfMonth": "$date" },
            "hour": { "$hour" },
            "minute": {
                "$subtract": [
                    { "$minute": "$date" },
                    { "$mod": [
                        { "$minute": "$date" },
                        15    
                    ]}
                ]
            }
        },
        "count": { "$sum": 1 }
    }}
])

Upvotes: 5

Related Questions