CODEWITHSUNDEEP
mongodbaggregation-framework
elofland
elofland

Reputation: 35

MongoDB date math with aggregation variable

I'm trying to build an aggregation of things that haven't reported in by some interval (heartbeat) - I need to calculate a value based on a stored heartbeat:

db.things.aggregate([ 
  {$project: {"lastmsg":1, "props.settings":1}}, 
  {$unwind: "$props.settings"},
  {$project: {
    _id:0, 
    "lastmsg": "$lastmsg", 
    "heartbeat": {$multiply: [{$toInt: "$props.settings.heartbeat"},2000]},
    "now": new Date(), "subtracted": new Date(new Date().getTime()- "$heartbeat")
  }
} 
])

Result returned is like this:

{ "lastmsg" : ISODate("2020-04-23T12:41:37.667Z"), "heartbeat" : 240000, "now" : ISODate("2020-05-14T16:26:11.824Z"), "subtracted" : ISODate("1970-01-01T00:00:00Z") }
{ "lastmsg" : ISODate("2020-05-14T16:24:24.228Z"), "heartbeat" : 240000, "now" : ISODate("2020-05-14T16:26:11.824Z"), "subtracted" : ISODate("1970-01-01T00:00:00Z") }

The "subtracted" projection is not doing the date math as expected. I can plug in a specific number and it works but this defeats the purpose...

As a last step I will match to see what of these things hasn't checked in within the interval of heartbeat:

  { $match: { "lastmsg":{$gte: "$subtracted")}

Any help would be greatly appreciated...

Upvotes: 1

Views: 116

Answers (1)

igorkf
igorkf

Reputation: 3565

I don't know how your data is like (you should post your data to help), but I think this can solve the problem.
You can use the $$NOW variable, that returns the current date in ISODate format.

Test data:

[
  {
    "lastmsg": ISODate("2020-04-23T12:41:37.667Z"),
    "heartbeat": 240000
  },
  {
    "lastmsg": ISODate("2020-05-14T16:24:24.228Z"),
    "heartbeat": 240000
  }
]

Query:

db.collection.aggregate([
  {
    $addFields: {
      "now": "$$NOW",
      "subtracted": {
        $subtract: [
          "$$NOW",
          "$heartbeat"
        ]
      }
    }
  },
  {
    $match: {
      "lastmg": {
        $gte: "$subtracted"
      }
    }
  }
])

Upvotes: 1

Related Questions