CODEWITHSUNDEEP
python-3.xmongodbaggregate
Utkarsh Dhawan
Utkarsh Dhawan

Reputation: 324

MongoDB Aggregate - Group by month on a field with timestamp(int)

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

Upvotes: 1

Views: 1334

Answers (2)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

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]}
                ]
            }
        }
    }
}

Upvotes: 2

Archana Agivale
Archana Agivale

Reputation: 405

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'
      }
    }
  }
]

Upvotes: 1

Related Questions