How do you get DayHours from Mongo date field?

Question

I am trying to group by DayHours in a mongo aggregate function to get the past 24 hours of data. For example: if the time of an event was 6:00 Friday the "DayHour" would be 6-5. I'm easily able to group by hour with the following query:

db.api_log.aggregate([
    { '$group': { 
        '_id': { 
            '$hour': '$time'
        }, 
        'count': { 
          '$sum':1 
        } 
      } 
    },
    { '$sort' : { '_id': -1 } }
  ])

I feel like there is a better way to do this. I've tried concatenation in the $project statement, however you can only concatenate strings in mongo(apparently). I effectively just need to end up grouping by day and hour, however it gets done. Thank You.

score 1 · Accepted Answer · answered Nov 05 '13 at 08:09

I assume that time field contains ISODate. If you want only last 24 hours you can use this:

var yesterday = new Date((new Date).setDate(new Date().getDate() - 1));

db.api_log.aggregate(
    {$match: {time: {$gt: yesterday}}},
    {$group: {
        _id: {
            hour: {$hour: "$time"},
            day: {$dayOfMonth: "$time"},
        },
        count: {$sum: 1}
    }}
)

If you want general grouping by day-hour you can use this:

db.api_log.aggregate(
    {$group: {
        _id: {
            hour: {$hour: "$time"},
            day: {$dayOfMonth: "$time"},
            month: {$month: "$time"},
            year: {$year: "$time"}
        },
        count: {$sum: 1}
    }}
)

score 0 · Answer 2 · edited May 23 '17 at 10:32

Also this is not an answer per se (I do not have mongodb now to come up with the answer), but I think that you can not do this just with aggregation framework (I might be wrong, so I will explain myself).

You can obtain date and time information from mongoId using .getTimestamp method. The problem that you can not output this information in mongo query (something like db.find({},{_id.getTimestamp}) does not work). You also can not search by this field (except of using $where clause).

So if it is possible to achieve, it can be done only using mapreduce, where in reduce function you group based on the output of getTimestamp.

If this is the query you are going to do quite often I would recommend actually adding date field to your document, because using this field you will be able properly aggregate your data and also you can use indeces not to scan all your collection (like you are doing with $sort -1, but to $match only the part which is bigger then current date - 24 hours).

I hope this can help even without a code. If no one will be able to answer this, I will try to play with it tomorrow.

How do you get DayHours from Mongo date field?

2 Answers2