mongodbcrudaggregation

add hours to a datetime field in mongodb


How do I add hours to a timestamp in a filtered set of documents in mongodb?

A few documents were entered into a collection with a timestamp of 13:00 Pacific Time (PDT) in error, rather than the intended timestamp of 20:00 UTC.
Goal is to add 7 hours to the timestamp of the affected documents.

Seems easy: find the affected documents (timestamp hour = 13) and then updateMany adding 7 hours to the timestamp.

How do I do that in one statement?

example documents, hour = 13 in first document:

  {
    "_id" : ObjectId("665e1e9f2c8963573b18c261"),
    "timestamp" : ISODate("2024-06-17T13:00:00.000+0000")
  }
  {
    "_id" : ObjectId("665e1e9f2c8963573b18c262"),
    "timestamp" : ISODate("2024-06-17T20:00:00.000+0000"),
   }

desired documents, hour = 20 in each document:

  {
    "_id" : ObjectId("665e1e9f2c8963573b18c261"),
    "timestamp" : ISODate("2024-06-17T20:00:00.000+0000")
  }
  {
    "_id" : ObjectId("665e1e9f2c8963573b18c262"),
    "timestamp" : ISODate("2024-06-17T20:00:00.000+0000"),
   }

I can find the documents affected:

   db.opts.aggregate({$project: {timestamp: "$timestamp", hour: {$hour: "$timestamp"}, new_ timestamp: {$add:["$timestamp", 7*60*60000 ]}}},{$match:{hour:13}},)

and can create a $set:

   $set: { timestamp: "$new_ timestamp" },

but how do I combine those in one updateMany?


Solution

  • There are 2 ways to do it, both through an aggregation pipeline in an update. In the selection query, select {$hour: "$timestamp"} = 13

    1. reparse the date with new timezone info
    db.collection.updateMany({
      $expr: {
        $eq: [
          13,
          {
            "$hour": "$timestamp"
          }
        ]
      }
    },
    [
      {
        "$set": {
          "timestamp": {
            "$dateFromParts": {
              "year": {
                "$year": "$timestamp"
              },
              "month": {
                "$month": "$timestamp"
              },
              "day": {
                "$dayOfMonth": "$timestamp"
              },
              "hour": {
                "$hour": "$timestamp"
              },
              "minute": {
                "$minute": "$timestamp"
              },
              "second": {
                "$second": "$timestamp"
              },
              "timezone": "-0700"
            }
          }
        }
      }
    ])
    

    Mongo Playground

    1. simply add 7 hours with $dateAdd
    db.collection.updateMany({
      $expr: {
        $eq: [
          13,
          {
            "$hour": "$timestamp"
          }
        ]
      }
    },
    [
      {
        "$set": {
          "timestamp": {
            "$dateAdd": {
              "startDate": "$timestamp",
              "unit": "hour",
              "amount": 7
            }
          }
        }
      }
    ])
    

    Mongo Playground