CODEWITHSUNDEEP
mongodbaggregation-framework
piisexactly3
piisexactly3

Reputation: 779

Group by calculated dates in MongoDB

I have data that looks like this:

[
  {
    "start_time" : ISODate("2017-08-22T19:43:41.442Z"), 
    "end_time" : ISODate("2017-08-22T19:44:22.150Z")
  },
  {
    "start_time" : ISODate("2017-08-22T19:44:08.344Z"),
    "end_time" : ISODate("2017-08-22T19:46:25.500Z") 
  }
]

Is there any way to run an aggregation query that will give me a frequency result like: 

{ 
   ISODate("2017-08-22T19:43:00.000Z"): 1,
   ISODate("2017-08-22T19:44:00.000Z"): 2,
   ISODate("2017-08-22T19:45:00.000Z"): 1,
   ISODate("2017-08-22T19:46:00.000Z"): 1
}

Essentially I want to group by minute, with a sum, but the trick is that each record might count toward multiple groups. Additionally, as in the case with 19:45, the date is not explicitly mentioned in the data (it is calculated as being between two other dates).

At first I thought I could do this with a function like $minute. I could group by minute and check to see if the data fits in that range. However, I'm stuck on how that would be accomplished, if that's possible at all. I'm not sure how to turn a single entry into multiple date groups.

Upvotes: 0

Views: 23

Answers (1)

s7vr
s7vr

Reputation: 75934

You can use below aggregation in 3.6 mongo version.

db.col.aggregate([
  {"$addFields":{"date":["$start_time","$end_time"]}},
  {"$unwind":"$date"},
  {"$group":{
    "_id":{
      "year":{"$year":"$date"},
      "month":{"$month":"$date"},
      "day":{"$dayOfMonth":"$date"},
      "hour":{"$hour":"$date"},
      "minute":{"$minute":"$date"}
    },
    "count":{"$sum":1}
  }},
  {"$addFields":{
    "_id":{
      "$dateFromParts":{
        "year":"$_id.year",
        "month":"$_id.month",
        "day":"$_id.day",
        "minute":"$_id.minute"
      }
    }
  }}
])

Upvotes: 2

Related Questions