MongoDB – get number of rows inserted per day

Was looking for a mongodb way to get the number of rows inserted by days in a collection. I spent a lot of time on this mixing many answers found on the web. Finally the result is working so you will find different way to do it in this post.

Basically my collection have a time field with a EPOC in ms timestamp as a LongInteger named time.

db.getCollection('raw').aggregate([
{ $match: { id : "15139", duplicate : "false" } },
{ $project: { 
      _id : 0, 
      firedm : { "$multiply" : [
                        { "$floor" :
                            { "$divide" : [ 
                                     "$time",
                                     24*3600*1000
                              ]
                            } 
                         
                       },
                       24*3600*1000
                 ]}
           }
}, 
{ $group: {
    _id : "$firedm",
    count : { $sum:1 }
}},
{ $sort : { count : -1 } }
])

I use aggregate function where you can put different parts:

  • $match is a query selector, here i’m looking for rows matching a certain ID and a certain value for on of my fields.
  • $project allows to make operations on the result of this match. we can have multiple sequential projects in an aggregate.
  • $group is the way I want to group my rows
  • $sort is the way I want to sort the result (here by number of rows / per )

The objective of the $project part is to define a new field in the result having the time slot element I want to group by. The way I make it was to round the timestamp existing in the field time by 1 day in ms ( 24*3600*1000). making this, all the date of the day belongs to midnight. This new field is named firedm and then use in the $group part.

This sounds like my best way to do it.

I’ve tried other ways previously : the following one is similar by the date generated is not a timestamp but a key based on YYMMDD format

db.getCollection('raw').aggregate([
{ $match: { id : "15139", duplicate : "false" } },
{ $project: { 
      _id : 0, 
      fired : { $add : [ new Date(0), "$time" ] }, 
    } 
}, 
{ $project : {
    year : { $year:"$fired" },
    month : { $month:"$fired"},
    day : { $dayOfMonth:"$fired"}
}},
{ $group: {
    _id : { year:"$year" , month:"$month", day:"$day" },
    count : { $sum:1 }
}},
{ $sort : { count : -1 } }
])

In this example, the $time field is transformed into a date by this line

fired : { $add : [ new Date(0), "$time" ] }

Then it is possible to call the time function $year, $month $dayOfMonth to compose a new id for grouping.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.