I have a collection like below.

{
"field1":"value1",
"created_at":"2022-01-01T11:42:01Z"
},
{
"field1":"value2",
"created_at":"2022-01-01T11:22:15Z"
}

I need to group the results by 15 minute time interval and project the results like below from this collection.

[{
"from":"2022-01-01T11:15:00Z",
"to":"2022-01-01T11:30:00Z",
"count":1
},
{
"from":"2022-01-01T11:30:00Z",
"to":"2022-01-01T11:45:00Z",
"count":1
}]

I am able to get the count by 15 minute time interval using the below query. But I want to project from and to dates as well.

db.collection.aggregate([
  { "$group": {
    "_id": {
      "year": { "$year": "$created_at" },
      "dayOfYear": { "$dayOfYear": "$created_at" },
      "hour": { "$hour": "$created_at" },
      "interval": {
        "$subtract": [ 
          { "$minute": "$created_at" },
          { "$mod": [{ "$minute": "$created_at"}, 15] }
        ]
      }
    }},
    "count": { "$sum": 1 }
  }}
])

CodePudding user response：

You can try an approach,

• $dateToParts get parts of the created_at date
• $group by year, month, day, hour, and interval as per mod and subtraction calculation and get the total count
• to get from and to date from interval you can use $dateFromParts operator, just to add 15 minutes into the date.

db.collection.aggregate([
  {
    $addFields: {
      created_at: { $dateToParts: { date: "$created_at" } }
    }
  },
  {
    $group: {
      _id: {
        year: "$created_at.year",
        month: "$created_at.month",
        day: "$created_at.day",
        hour: "$created_at.hour",
        interval: {
          $subtract: [
            "$created_at.minute",
            { $mod: ["$created_at.minute", 15] }
          ]
        }
      },
      count: { $sum: 1 }
    }
  },
  {
    $project: {
      _id: 0,
      count: 1,
      from: {
        $dateFromParts: {
          year: "$_id.year",
          month: "$_id.month",
          day: "$_id.day",
          hour: "$_id.hour",
          minute: "$_id.interval"
        }
      },
      to: {
        $dateFromParts: {
          year: "$_id.year",
          month: "$_id.month",
          day: "$_id.day",
          hour: "$_id.hour",
          minute: { $add: ["$_id.interval", 15] }
        }
      }
    }
  }
])

Playground