MongoDB aggregate records group by 15 minutes from current time-CodePudding

I am trying to get data by grouping every 15 minutes interval from the current time. Currently it is returning me result base of literally 15 minutes. Examples: 9:00, 9:15, 9:30, 9:45, 10:00, 10:15, 10:30, and so on.

What I am trying to achieve is that the interval to start from the current time. Say now it is 9:08, the intervals should be 9:08, 9:23, 9:38, 9:53, 10:08, 10:23 and so on

Stat_Snapshots.aggregate([
        {
            $group:
            {
                _id: {
                    "$toDate": {
                        "$subtract": [
                            { "$toLong": "$date" },
                            { "$mod": [{ "$toLong": "$date" }, 1000 * 60 * 15] }
                        ]
                    }
                }
                ,
                floorPrice: { "$min": "$floorPrice" },
                listedCount: { "$min": "$listedCount" }
            },

        }
    ])

Current Result

            {
                "_id": "2022-09-28T07:30:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 375
            },
            {
                "_id": "2022-09-28T07:15:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 376
            },
            {
                "_id": "2022-09-28T07:00:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 375
            },
            {
                "_id": "2022-09-28T06:45:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 375
            }

Sample Input Document

{
    "_id" : ObjectId("62fb40acd762c3e1150e0133"),
    "collections" : ObjectId("62fb24130d026bd9140c3f3a"),
    "floorPrice" : 297500000,
    "listedCount" : "259",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e0135"),
    "collections" : ObjectId("62fb39140d026bd9140c44d2"),
    "floorPrice" : -1369803776,
    "listedCount" : "101",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e0137"),
    "collections" : ObjectId("62fb38fc0d026bd9140c44cd"),
    "floorPrice" : 1010065408,
    "listedCount" : "1017",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e0139"),
    "collections" : ObjectId("62fb38da0d026bd9140c44c8"),
    "floorPrice" : 95032704,
    "listedCount" : "327",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e013b"),
    "collections" : ObjectId("62fb24030d026bd9140c3f32"),
    "floorPrice" : -1189934592,
    "listedCount" : "273",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb4430d762c3e1150e013e"),
    "collections" : ObjectId("62fb39140d026bd9140c44d2"),
    "floorPrice" : -1369803776,
    "listedCount" : "100",
    "date" : ISODate("2022-08-16T07:16:00.033Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb4430d762c3e1150e0140"),
    "collections" : ObjectId("62fb38da0d026bd9140c44c8"),
    "floorPrice" : -4967296,
    "listedCount" : "325",
    "date" : ISODate("2022-08-16T07:31:00.033Z"),
    "__v" : 0
}

Is there something that I am missing that causes this outcome?

CodePudding user response：

One option is to modify your query:

db.collection.aggregate([
  {$group: {
      _id: {
        $toDate: {
          $subtract: [
            {$toLong: new Date()},
            {$multiply: [
              {$ceil: {
                  $divide: [
                    {$subtract: [{$toLong: new Date()}, {$toLong: "$date"}]}, 
                    1000 * 60 * 15]
                  }
                }, 1000 * 60 * 15]
            }
          ]
        }
      },
      floorPrice: {
        $min: "$floorPrice"
      },
      listedCount: {
        $min: "$listedCount"
      }
    }
  }
])

See how it works on the playground example

Or with modern mongoDB:

db.collection.aggregate([
  {$group: {
    _id: {
      $dateAdd: {
        startDate: new Date(),
        unit: "minute",
        amount: {
          $multiply: [
            {$ceil: {
              $divide: [
                {$dateDiff: {startDate: "$date", endDate: new Date(), unit: "minute"}},  
                15]
            }}, -15]
          }
        }
      },
      floorPrice: {
        "$min": "$floorPrice"
      },
      listedCount: {
        "$min": "$listedCount"
      }
    }
  }
])

See how it works on the playground example