add hours to a datetime field in mongodb

Question

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?

ray · Accepted Answer · 2024-06-17 17:14:46Z

2

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

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

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

answered Jun 17, 2024 at 17:14

ray

15.6k12 gold badges97 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fip Over a year ago

Bingo! Works great - thank you!

Collectives™ on Stack Overflow

add hours to a datetime field in mongodb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related