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?