I have a collection of workorders where it have time_started and time_completed values. I want to have auto-calculated field called duration that automatically calculated time_completed - time_started. What is the best way? Essentially what I want is, when App post requests with a completed time, my duration is auto calculated. Example data

router.post('/completed', function (req, res) {
  const time_completed = req.body.time_completed
  const workorder_id = req.body.workorder_id

  db.collection(workorder).updateOne(
    { _id: ObjectId(workorder_id) },

    {
      $set: {
        time_completed: time_completed,
      }
    },

    function (err, result) {
      if (err) throw err
      res.send('Updated')
    }
  )
});

CodePudding user response：

Query

• pipeline update requires >= MongoDB 4.2
• add the time_completed
• add the duration also

*replace the 6 with the javascript variable that holds the time_completed Date
*duration will be in milliseconds

Test code here

db.collection.update(
{"_id": 1},
[
  {
    "$set": {
      "time_completed": 6,
      "duration": {
        "$subtract": [
          6,
          "$time_started"
        ]
      }
    }
  }
])

Edit

You have strings on your database, i thought it was dates, best thing to do is to convert all those string-dates to Date with $dataFromString like the code bellow, and use the first query.

To get the string if you needed from Date you can do $stringFromDate when you need it.

Query

• same like above but it converts string dates to Date to do the substraction (keeps the dates in strings inside the database)

Test code here

db.collection.update({
  "_id": 1
},
[
  {
    "$set": {
      "time_completed": "2021-11-21T00:00:00.000Z",
      "duration": {
        "$subtract": [
          ISODate("2021-11-21T00:00:00Z"),
          {
            "$dateFromString": {
              "dateString": "$time_started"
            }
          }
        ]
      }
    }
  }
])