I'm trying to group documents by date with the Mongo aggregation framework. I've tried a bunch of things and can't identify the problem. My query looks like this:
// get current date and then get the date 8 months ago
var now = Date.now()
var lastMonth = 60*60*24*30*1000*8
lastMonth = now - lastMonth
var query = {
type: 'something',
deleted: {$ne: true},
created: {$gte: lastMonth}
}
var stickerNames = [];
db.Message.aggregate([{$match: query},
{$group:
{_id: {data: '$data',
month: {$month: '$created'},
year: {$year: '$created'}
},
count:{$sum: 1}
}
},
{$sort:
{count: 1}}],
function(err, stuff){
if(err){
return res.json({error: err.message})
}
else{
return res.json({data: stuff})
}
})
I've tried a ton of different things with the dates and checked other SO posts, but nothing has worked so far. If I take out the created: {$gte: lastMonth} clause then it returns every document of type 'something'. If I leave it, it returns no documents.
The created field is set in the model by
created: {type: Date, default: Date.now}
I'm trying to group documents by date with the Mongo aggregation framework. I've tried a bunch of things and can't identify the problem. My query looks like this:
// get current date and then get the date 8 months ago
var now = Date.now()
var lastMonth = 60*60*24*30*1000*8
lastMonth = now - lastMonth
var query = {
type: 'something',
deleted: {$ne: true},
created: {$gte: lastMonth}
}
var stickerNames = [];
db.Message.aggregate([{$match: query},
{$group:
{_id: {data: '$data',
month: {$month: '$created'},
year: {$year: '$created'}
},
count:{$sum: 1}
}
},
{$sort:
{count: 1}}],
function(err, stuff){
if(err){
return res.json({error: err.message})
}
else{
return res.json({data: stuff})
}
})
I've tried a ton of different things with the dates and checked other SO posts, but nothing has worked so far. If I take out the created: {$gte: lastMonth} clause then it returns every document of type 'something'. If I leave it, it returns no documents.
The created field is set in the model by
created: {type: Date, default: Date.now}
Share
Improve this question
asked May 11, 2015 at 4:17
user137717user137717
2,1854 gold badges28 silver badges51 bronze badges
1
-
2
A Date object minus number returns the timestamp value and not the number of milliseconds. You should be doing:
lastMonth = new Date(now - lastMonth)
` – Blakes Seven Commented Jul 5, 2015 at 23:45
1 Answer
Reset to default 8As far as I understand, "group by date" means group by "day-month-year". Maybe are you missing the $dayOfMonth
aggregation function?
Given this sample data set:
> db.w.find({})
{ "_id" : ObjectId("55503d31a3d32cf6295ad897"),
"created" : ISODate("2014-12-08T12:00:00Z"),
"data" : 1 }
{ "_id" : ObjectId("55503d43a3d32cf6295ad898"),
"created" : ISODate("2014-12-08T08:00:00Z"),
"data" : 2 }
{ "_id" : ObjectId("55503d4ba3d32cf6295ad899"),
"created" : ISODate("2014-12-08T20:00:00Z"),
"data" : 3 }
{ "_id" : ObjectId("55503d55a3d32cf6295ad89a"),
"created" : ISODate("2014-12-06T20:00:00Z"),
"data" : 4 }
{ "_id" : ObjectId("55503d77a3d32cf6295ad89c"),
"created" : ISODate("2015-12-08T20:00:00Z"),
"data" : 5 }
And that aggregate pipeline:
> db.w.aggregate([
{$group:
{
_id:
{
day: {$dayOfMonth: "$created"},
month: {$month: "$created"},
year: {$year: "$created"}
},
total: {$sum: "$data"},
count: {$sum: 1}
}
},
{$sort: {count: 1}}
])
You will get back the expected oute:
{ "_id" : { "day" : 8, "month" : 12, "year" : 2015 }, "total" : 5, "count" : 1 }
{ "_id" : { "day" : 6, "month" : 12, "year" : 2014 }, "total" : 4, "count" : 1 }
{ "_id" : { "day" : 8, "month" : 12, "year" : 2014 }, "total" : 6, "count" : 3 }