CODEWITHSUNDEEP
node.jsmongodbmongoose
geezmo
geezmo

Reputation: 161

Mongoose aggregate - $subtract dates

I'm having troubles with Mongoose aggregation when trying to subtract two dates to get milliseconds of the difference.

Here's the document's structure:

DeliverySchema = new mongoose.Schema({
  hash: String,
  status: Number,
  insertedOn: {
    type: Date,
    default: Date.now
  },
  endedOn: {
    type: Date
  }
}, {
  collection: 'Deliveries'
});

This is the query:

var Delivery = mongoose.model('Delivery');

return Delivery.aggregate([
    {
      $match: {
        'insertedOn': {$ne: null, $type: 9},
        'endedOn': {$ne: null, $type: 9}
      }
    },
    {$project: {duration: {$subtract: ["$endedOn", "$insertedOn"]}}},
    {
      $group: {
        _id: {
          day: {$dayOfMonth: "$insertedOn"},
          month: {$month: "$insertedOn"},
          year: {$year: "$insertedOn"}
        },
        count: {$sum: 1},
        durationAvg: {$avg: "$duration"}
      }
    },
    {$sort: {"_id.year": 1, "_id.month": 1, "_id.day": 1}}
  ])

This is the error i'm getting:

error: exception: can't convert from BSON type EOO to Date

The $match phase should filter all documents with insertedOn/endedOn fields null or non-Date, but it seems useless. I'm stuck, does anybody have any clues? I'm using Mongoose 4.0.1 (mongod 2.6.9 hosted on Mongolab) with Node 0.10.33. Thanks

Thanks.

Upvotes: 4

Views: 4399

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312149

The output of your $project stage is docs that contain only the computed duration field. You need to also include the insertedOn and endedOn fields so that they're available for use in the $group stage that follows:

{$project: {
    duration: {$subtract: ["$endedOn", "$insertedOn"]},
    endedOn: 1,
    insertedOn: 1
}}

Upvotes: 3

Related Questions