CODEWITHSUNDEEP
mongodbaggregation-framework
Sixthpoint
Sixthpoint

Reputation: 1193

Group by time interval between window of time

I have the following document structure:

{  
   "_id":"5c59c35d8610f702d00e6f70",
   "ipAddress":"50.116.14.48",
   "startTime":"2018-02-06T12:01:59.000Z",
   "endTime":"2018-02-06T12:31:00.000Z", 
}

I would like to be able to group occurrences within a 15 min window of time. For example; Given the structure of the document above I would assume this document would count as an occurrence for both 0-15mins, 15-30mins, and 30-45mins.

The result would look something like this:

[  
   {  
      "occurrences":1,
      "startWindow":"2018-02-06T12:00:00.000Z",
      "endWindow":"2018-02-06T12:15:00.000Z"
   },
   {  
      "occurrences":1,
      "startWindow":"2018-02-06T12:15:01.000Z",
      "endWindow":"2018-02-06T12:30:00.000Z"
   },
   {  
      "occurrences":1,
      "startWindow":"2018-02-06T12:30:01.000Z",
      "endWindow":"2018-02-06T12:45:00.000Z"
   }
]

I have seen many examples that group only by a single date on an interval, but how about this situation where the document has a window of time?

How do you build this aggregation?

Upvotes: 1

Views: 432

Answers (2)

Alex Blex
Alex Blex

Reputation: 37018

In addition to mickl's time math you need to use $range to "spread" the document across all "windows" between start and end:

db.col.aggregate([
    { $addFields: {
        // an array of 15 min intervals between startTime and endTime
        window: { $range: [ 
            { $floor: { $divide: [ { $toLong: { $toDate: "$startTime" } }, 900000 ] }  }, 
            { $ceil: { $divide: [ { $toLong: { $toDate: "$endTime" } }, 900000 ] }  }
        ] }
    } },
    // 1 document per interval
    { $unwind: "$window" },
    // group by interval
    { $group: {
        _id: "$window",
        occurrences: { $sum: 1 }
    }},
    // to match expected order
    {$sort: {_id:1}},
    // calculate window boundaries
    { $project: {
        _id: 0,
        occurrences: 1,
        startWindow: { $toDate: { $add: [ { $multiply: [ "$_id", 900000 ] }, 1000 ] } },
        endWindow: { $toDate: { $multiply: [ { $add: [ "$_id", 1 ] }, 900000 ] } }        
    } }
])

Upvotes: 2

mickl
mickl

Reputation: 49945

It's easy if you can operate on miliseconds instead of strings. To convert your startTime and endTime to the number of miliseconds you can use $toDate and $toLong operators (MongoDB 4.0 or newer).

The formula to "classify" you date to 15-minutes range is also pretty simple: 15 minutes is equal to 900000 miliseconds so you can use $mod to get the value that needs to be $subtract -ed from original date.

Then from every document you can generate two documents (for startDate and endDate) using $unwind and then $group them.

db.col.aggregate([
    {
        $project: {
            startTime: { $toLong: { $toDate: "$startTime" } },
            endTime: { $toLong: { $toDate: "$endTime" } }
        }
    },
    {
        $project: {
            ranges: [
                { 
                    start: { $subtract: [ "$startTime", { $mod: [ "$startTime", 900000 ] } ] },
                    end: { $add: [ { $subtract: [ "$startTime", { $mod: [ "$startTime", 900000 ] } ] }, 900000 ] }
                },
                { 
                    start: { $subtract: [ "$endTime", { $mod: [ "$endTime", 900000 ] } ] },
                    end: { $add: [ { $subtract: [ "$endTime", { $mod: [ "$endTime", 900000 ] } ] }, 900000 ] }
                }
            ]
        }
    },
    {
        $unwind: "$ranges"
    },
    {
        $group: {
            _id: "$ranges",
            count: { $sum: 1 }
        }
    },
    {
        $project: {
            _id: 0,
            occurences: "$count",
            startWindow: { $toString: { $toDate: "$_id.start" } },
            endWindow: { $toString: { $toDate: "$_id.end" } }
        }
    }
])

Upvotes: 2

Related Questions