Get number of operations from each X minutes intervals from mongo databbase, Even non operational minutes data also

Question

Hello I have a MongoDB aggregation problem, I want to generate a report of no. of operations done in the database, using aggregation stages $match and $group in match providing intervals $gte & $lte, in group trying to get data of each 15 minutes no. of operations EVEN 0 OPERATIONS report also need.

** I have used aggregation like this**

starttime = datetime.strptime(request.args.get('start'), '%Y-%m-%d-%H-%M-%S')
    endtime = datetime.strptime(request.args.get('end'), '%Y-%m-%d-%H-%M-%S')

    reportView = []
    pipeline = [{
        "$match": {
            "time": { "$gte": starttime, "$lte": endtime}}
    },
    { "$sort": { "time": -1 } },

        { "$group": {
      "_id": {
          "$toDate": {
              "$subtract": [
                  { "$toLong": { "$toDate": "$time" }  },
                  { "$mod": [ { "$toLong": { "$toDate": "$time" } }, 1000 * 60 * 15 ] }
              ]
          }
      },
      "count": { "$sum": 1 }
    }}
    ] 

getting this output :

Heading

[
  {
    "_id": "Sat, 12 Oct 2019 16:30:00 GMT",
    "count": 1
  },
  {
    "_id": "Tue, 22 Oct 2019 13:15:00 GMT",
    "count": 1
  },
  {
    "_id": "Fri, 01 Nov 2019 19:00:00 GMT",
    "count": 1
  },
  {
    "_id": "Thu, 31 Oct 2019 11:15:00 GMT",
    "count": 1
  }]

###### not getting the non operational interval ##### enter code here

Heading ##but i need output like this :

[
{
"_id": "Sat, 12 Oct 2019 16:30:00 GMT",
"count": 1
},
{
"_id": "Sat, 12 Oct 2019 16:45:00 GMT",
"count": 0
},
{
"_id": "Sat, 12 Oct 2019 17:00:00 GMT",
"count": 0
},
{
"_id": "Sat, 12 Oct 2019 17:15:00 GMT",
"count": 0
},
{
"_id": "Sat, 12 Oct 2019 17:30:00 GMT",
"count": 1
},
{
"_id": "Sat, 12 Oct 2019 17:45:00 GMT",
"count": 5
},
{
"_id": "Sat, 12 Oct 2019 18:00:00 GMT",
"count": 0
},
{
"_id": "Sat, 12 Oct 2019 18:15:00 GMT",
"count": 0
}]

Answer 1

You have to construct your time range value before the query, because it is in fact a parameter of your query, not depending of documents. Doing this, you can use $buckets to group your document from their date.

db.collection.aggregate([
  {
    $bucket: {
      boundaries: [
        new Date("2019-11-13T00:00:00Z"),
        new Date("2019-11-13T00:15:00Z"),
        new Date("2019-11-13T00:30:00Z"),
        new Date("2019-11-13T00:45:00Z"),

      ],
      default: "outOfRange",
      groupBy: "$date",
      output: {
        count: {
          $sum: 1
        }
      }
    }
  },])

This will output something like this :

    [
  {
    "_id": "outOfRange",
    "count": 2
  },
  {
    "_id": ISODate("2019-11-13T00:00:00Z"),
    "count": 2
  },
  {
    "_id": ISODate("2019-11-13T00:15:00Z"),
    "count": 1
  },
  {
    "_id": ISODate("2019-11-13T00:30:00Z"),
    "count": 1
  }
]

IMPORTANT NOTE : the first output document ("_id": ISODate("2019-11-13T00:00:00Z")) will counts documents where ISODate("2019-11-13T00:00:00Z") <= $date < ISODate("2019-11-13T00:15:00Z")

You can test the POC here