CODEWITHSUNDEEP
javascriptmongodb
Sam
Sam

Reputation: 557

Can't convert from BSON type int to Date

I have this structure of a collection. Mongodb structure

I'd like to group the collections by month based on date_created field with this code.

db.activities.aggregate(
   [     
      { $match: { type: "Expense", "user_hash": "xxx" } },     
      {   
        $group: { 
           _id: { month: { $month: "$date_created" } },            
           total: { $sum: "$amount" }          
        }      
      },   
   ]  
);

I got this Exception in progress.

2020-07-19T23:13:01.652+0700 E  QUERY    [js] uncaught exception: Error: command failed: {
    "operationTime" : Timestamp(1595175178, 1),
    "ok" : 0,
    "errmsg" : "can't convert from BSON type int to Date",
    "code" : 16006,
    "codeName" : "Location16006",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1595175178, 1),
        "signature" : {
            "hash" : BinData(0,"xxx"),
            "keyId" : NumberLong("xxx")
        }
    }
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:583:17
assert.commandWorked@src/mongo/shell/assert.js:673:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1012:12
@(shell):1:1

Any solution? Thank you in Advance.

Upvotes: 1

Views: 2067

Answers (1)

thammada.ts
thammada.ts

Reputation: 5245

You'll have to convert the data from type int to Date. You can do that using the $toDate operator (available from MongoDB v4.0).

Apparently you are storing the timestamps in seconds, so you'll have to multiply by 1000 to obtain timestamps in milliseconds before passing it to $toDate

db.activities.aggregate(
   [     
      { $match: { type: "Expense", "user_hash": "xxx" } },     
      {   
        $group: { 
           _id: {
               month: {
                   $month: { $toDate: { $multiply: ["$date_created", 1000] } }
               }
           },            
           total: { $sum: "$amount" }          
        }      
      },   
   ]  
);

Upvotes: 6

Related Questions