I am unable to aggregate a collection with month wise grouping on field with value as timestamp.
Collection(Dummy):
{
"created_at": 1232341243
}, {...}, ...
Now if I want to aggregate the documents of this collection on month using $month it throws the following error:
db.getCollection('dummy').aggregate([{"$group": {"_id": {"$month": "$created_at"}}}])
Error message
"errmsg" : "can't convert from BSON type int to Date",
Version of MongoDB: 3.6.3
You can first convert the "created_at" timestamp to date using "addFields" stage,
{
$addFields: {
convertedDate: {"$toDate":"$created_at"}
}
}
after that, you can do further operations. So finally your query for aggregation will be something like, For Version 3.6
[
{
'$project': {
"convertedDate": { "$add": [ new Date(0), "$created_at" ] }
}
},
{
'$project': {
'month': {
'$month': '$convertedDate'
},
'year': {
'$year': '$convertedDate'
}
}
}, {
'$group': {
'_id': {
'month': '$month',
'year': '$year'
},
'total': {
'$sum': 1
},
'month': {
'$first': '$month'
},
'year': {
'$first': '$year'
}
}
}
]
For Version 4+
[
{
'$addFields': {
'convertedDate': {"$toDate":"$created_at"}
}
},
{
'$project': {
'month': {
'$month': '$convertedDate'
},
'year': {
'$year': '$convertedDate'
}
}
}, {
'$group': {
'_id': {
'month': '$month',
'year': '$year'
},
'total': {
'$sum': 1
},
'month': {
'$first': '$month'
},
'year': {
'$first': '$year'
}
}
}
]
Let's start by seeing what types of input $month supports:
A Date, a Timestamp, or an ObjectID.
So for Mongo version 4.0+ you can use $toDate to convert a valid (number) ts or a valid date string into a Date object like so:
{"$group": {"_id": {"$month": {"$toDate":"$created_at"}}}}
As you're using an earlier Mongo version and don't have access to this function You can use a "hacky" aggregation using $add as when it get's a number and a date type it will return a date type.
The only caveat is that you'll need to convert your timestamp (which is represented with seconds from epoch 0) to miliseconds. i.e multiply by a 1000, like so:
from datetime import datetime
...
{
"$group": {
"_id": {
"$month": {
'$add': [
datetime(1970, 1, 1),
{'$multiply': ["$created_at", 1000]}
]
}
}
}
}