Mongodb aggregate: convert date to another timezone

MongoDB 3.6 added timezone parameter to the date manipulation operators. See Kevin's answer.

We can add the "timestamp" to 7 * 60 * 60 * 1000 in a $project stage.

The following pipeline seems to work in MongoDB 3.4 or older.

db.collection.aggregate([
    { "$project": {
        "year": { "$year": { "$add": [ "$timestamp", 7 * 60 * 60 * 1000 ] } }, 
        "month": { "$month": { "$add": [ "$timestamp", 7 * 60 * 60 * 1000 ] } }, 
        "day": { "$dayOfMonth": { "$add": [ "$timestamp", 7 * 60 * 60 * 1000 ] } } 
    } }
])

Hi sharing my solution used on one of my extracts using mongodb version 3.4 or below and need it to covert from utc to manila time +08:00

see the sample query below

db.collection.aggregate([
{$project:{
   "created_at":{"$dateToString": { "date":{ "$add": [ "$yourtimestampfield", 8 * 60 * 60 * 1000 ] }, "format": "%Y-%m-%d %H:%M:%S"}},
}}])

enter image description here


As an update, MongoDB 3.6 has a new timezone parameter for date manipulation in the aggregation framework. Most date-related operators accept this optional parameter, see $hour for one example.

For example, if we have a document where the date is exactly the new year in UTC:

> db.test.find()
{"_id": 1, "dt": ISODate("2018-01-01T00:00:00Z")}

We can display the date in New York timezone:

> db.test.aggregate([
...     {$project:{
...         date:{$dayOfMonth:{date:'$dt',timezone:'America/New_York'}},
...         month:{$month:{date:'$dt',timezone:'America/New_York'}},
...         year:{$year:{date:'$dt',timezone:'America/New_York'}},
...         hour:{$hour:{date:'$dt',timezone:'America/New_York'}}
...     }}
... ])
{ "_id": 1, "date": 31, "month": 12, "year": 2017, "hour": 19 }

We can also display the date in Sydney timezone:

> db.test.aggregate([
...     {$project:{
...         date:{$dayOfMonth:{date:'$dt',timezone:'Australia/Sydney'}},
...         month:{$month:{date:'$dt',timezone:'Australia/Sydney'}},
...         year:{$year:{date:'$dt',timezone:'Australia/Sydney'}},
...         hour:{$hour:{date:'$dt',timezone:'Australia/Sydney'}}
...     }}
... ])
{ "_id": 1, "date": 1, "month": 1, "year": 2018, "hour": 11 }

The timezone description is using the standard Olson Timezone Identifier string.