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?
There are 2 ways to do it, both through an aggregation pipeline in an update. In the selection query, select {$hour: "$timestamp"}
= 13
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"
}
}
}
}
])
$dateAdd
db.collection.updateMany({
$expr: {
$eq: [
13,
{
"$hour": "$timestamp"
}
]
}
},
[
{
"$set": {
"timestamp": {
"$dateAdd": {
"startDate": "$timestamp",
"unit": "hour",
"amount": 7
}
}
}
}
])