How to shift date field in mongodb for all records in a collection?

userhxYp 0
Viewed 21

We have a demo site with some static data that keeps aging off and I'd like to run a mongo query to shift the dates by 30 days. Here is an example of the data. Using mongodb 4.4.3

{
    "_id" : "62bda37a4d28f5df2e0ab07f",
    "qty" : 1,
    "dateCreated" : ISODate("2022-06-30T13:22:02.368Z"),
    "price" : 4230.0,
    "ownership" : "Owned",
    "itemType" : "EXPIRING_SOON",
    "expirationDate" : ISODate("2022-07-21T04:00:00.000Z"),
    "serialNumber" : "20202868089"
}
mongodb-query mongodb
2 Answers

You can try the following:

db.collection.aggregate([
  {
    "$addFields": {
      "expirationDate": {
        "$toDate": {
          "$subtract": [
            {
              "$toLong": "$expirationDate"
            },
            2592000000
          ]
        }
      }
    }
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "replace"
    }
  }
])

Here, we re-compute the expirationDate field, by first subtracting 259200000 milliseconds (30 days) from the current expiration date, and then we use $merge, to update the collection as well. You can use $add instead of $subtract to increase the expiry date as well. Here is the playground link.

You can also use the update function to perform this if you want the whole operation to be atomic. Like this:

db.collection.update({},
[
  {
    "$set": {
      "expirationDate": {
        "$toDate": {
          "$subtract": [
            {
              "$toLong": "$expirationDate"
            },
            2592000000
          ]
        }
      }
    }
  }
],
{
  multi: true
})

See it working here.

Charchit Kapoor0

See the $dateSubtract aggregation operation. This with a $merge will do the trick.

Borda0
Related