[MongoDB]: Aggregation on Date Part of a DateTime Property

If you need to do an aggregation in MongoDB on a DateTime property and you want to aggregate only by Date part,

you can use a simple trick and use a filter to compose the date part before the grouping.
rs1:PRIMARY> db.dbversitycol.insert({ “_id” : “1”, “LastUpdatedOn” : new Date() , “company” : “microsoft” })
rs1:PRIMARY> db.dbversitycol.insert({ “_id” : “2”, “LastUpdatedOn” : new Date() , “company” : “google” })
rs1:PRIMARY> db.dbversitycol.insert({ “_id” : “3”, “LastUpdatedOn” : new Date() , “company” : “ibm” })
rs1:PRIMARY> db.dbversitycol.insert({ “_id” : “4”, “LastUpdatedOn” : new Date() , “company” : “cisco” })
rs1:PRIMARY> db.dbversitycol.insert({ “_id” : “5”, “LastUpdatedOn” : new Date() , “company” : “dbversity.com” })
rs1:PRIMARY>
rs1:PRIMARY> db.dbversitycol.find()
{ “_id” : “1”, “LastUpdatedOn” : ISODate(“2014-11-28T13:09:13.203Z”), “company” : “microsoft” }
{ “_id” : “2”, “LastUpdatedOn” : ISODate(“2014-11-28T13:09:13.207Z”), “company” : “google” }
{ “_id” : “3”, “LastUpdatedOn” : ISODate(“2014-11-28T13:09:13.210Z”), “company” : “ibm” }
{ “_id” : “4”, “LastUpdatedOn” : ISODate(“2014-11-28T13:09:13.213Z”), “company” : “cisco” }
{ “_id” : “5”, “LastUpdatedOn” : ISODate(“2014-11-28T13:09:14.035Z”), “company” : “dbversity.com” }
rs1:PRIMARY>
rs1:PRIMARY>

Here is the aggregation code for that :

rs1:PRIMARY> db.dbversitycol.aggregate(
… {
… “$project” :
… {
… _id : 0,
… “datePartDay” : {“$concat” : [
… {“$substr” : [{“$dayOfMonth” : “$LastUpdatedOn”}, 0, 2]}, “-“,
… {“$substr” : [{“$month” : “$LastUpdatedOn”}, 0, 2]}, “-“,
… {“$substr” : [{“$year” : “$LastUpdatedOn”}, 0, 4]}
… ] }
… }
… },
… { “$group” :
… { “_id” : “$datePartDay”, “Count” : { “$sum” : 1 } }
… }
… )
{ “result” : [ { “_id” : “28-11-2014”, “Count” : 5 } ], “ok” : 1 }
rs1:PRIMARY>
rs1:PRIMARY>

We can construct a pipeline where the first stage create a document with a new property that is composed only by the day-month-year. That new document can have id equal to zero if we only need a count based on that date field, or you can assign original id of the document or whatever else. The subsequent stage of the pipeline is a simple count where the id is the new field that contains only date part of the original DateTime property.
To get the number count per month/year
rs1:PRIMARY> db.dbversitycol.aggregate(
… {$project : {
… year : {$year : “$LastUpdatedOn”},
… month : {$month : “$LastUpdatedOn”}
… }},
… {$group : {
… _id : {year : “$year”, month : “$month”},
… count : {$sum : 1}
… }}
… )
{
“result” : [
{
“_id” : {
“year” : 2014,
“month” : 11
},
“count” : 5
}
],
“ok” : 1
}
rs1:PRIMARY>

You can check mongoDB supported data formats at MongoDB-DateFormat