Group by hour and count MongoDB

Question

I'm on a project working with data of a bike-sharing service. Each trip has the following info

> db.bikes.find({bike:9990}).pretty()
{
    "_id" : ObjectId("5bb59fd8e9fb374bf0cd5c1c"),
    "gender" : "M",
    "userAge" : 49,
    "bike" : 9990,
    "depStation" : 150,
    "depDate" : "01/08/2018",
    "depHour" : "0:00:13",
    "arrvStation" : 179,
    "arrvDate" : "01/08/2018",
    "arrvHour" : "0:23:38"
}

How do I group for each hour of the day and count the number of trips made in that specific hour? I'm trying with this query

db.bikes.aggregate(
  { 
     $group:{_id:{$hour: "$depHour"}, trips:{$sum: 1}}
  }
)

But it throws this error

    "ok" : 0,
    "errmsg" : "can't convert from BSON type string to Date",
    "code" : 16006,
    "codeName" : "Location16006"

Answer 1

You can try below aggregation with mognodb 4.0

db.collection.aggregate([
  { "$group": {
    "_id": {
      "$dateToString": {
        "date": {
          "$toDate": { "$concat": ["$depDate", "T", "$depHour"] }
        },
        "format": "%Y-%m-%d-%H"
      }
    },
    "trips": { "$sum": 1 }
  }}
])

Answer 2

The depDate and depHour fields are all string values that denote the day and the hour respectively so there is no need to use the date operators to convert the fields to date objects, all you need is to extract the hour part using $substrCP and then use them directly as expressions in your $group _id as:

db.bikes.aggregate([
    { '$group': {
        '_id': {
            'day': '$depDate',
            'hour': { '$substrCP': [ '$depHour', 0, 2 ] }
        }, 
        'trips': { '$sum': 1 }
    } }
])