Mongodb aggregate: convert date to another timezone

Question

I save my transaction with something like :

{code: "A", total: 250000, timestamp: ISODate("2016-01-20T23:57:05.771Z")},
{code: "B", total: 300000, timestamp: ISODate("2016-01-20T05:57:05.771Z")}

each of transaction has timestamp field under UTC timezone in it. Since I live in Jakarta (UTC+7) timezone, I need to add 7 hours to my timestamp before aggregation. Here's my mongo syntax:

db.transaction.aggregate(
[
 {
   $project:
     {
       year: { $year: "$timestamp" },
       month: { $month: "$timestamp" },
       day: { $dayOfMonth: "$timestamp" }
     }
 }
])

It returns:

    {
        "_id" : ObjectId("56a01ed143f2fd071793d63b"),
        "year" : 2016,
        "month" : 1,
        "day" : 20
    },
    {
        "_id" : ObjectId("56a01ed143f2fd071793d63b"),
        "year" : 2016,
        "month" : 1,
        "day" : 20
    }

which is wrong since the first transaction (code A), is happened at 21st January, but since it was converted to UTC (-7 Hours), it became ISODate("2016-01-20T23:57:05.771Z")

Note: I'm aware about the same problem over here, here's what I've been tried so far:

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

but it returns can't convert from BSON type NumberDouble to Date error. Any suggestions?

Could you accept Kevin's answer? – styvane May 18 '20 at 15:35 — styvane, May 18 '20 at 15:35

score 39 · Answer 1 · answered Jan 31 '18 at 06:31

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.

styvane · Accepted Answer · 2020-05-18T15:33:02.897

9

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 ] } } 
    } }
])

edited May 18 '20 at 15:33

answered Jan 21 '16 at 06:56

styvane

59,869
19
150
156

1

It's a good solution when you have only one time-zone but will not work for dynamic timezone. – Gopal Joshi Dec 22 '17 at 13:01
2

MongoDB 3.6 now supports this feature, see @Kevin's answer. – Michael Mikhjian Jul 03 '18 at 23:23
@MKNWebSolutions, You are right, the timezone parameter was added in 3.6. Kevin already got my vote. But this solutions seems to be ok if you haven't upgraded your mongod yet. – styvane Jul 04 '18 at 07:14

Pradeepvina · Answer 3 · 2023-02-25T01:37:01.603

4

You can use the $dateToString method with $project to get specified timezone result.

$project:{
     created_at: {
            $dateToString: {
              date: '$createdAt',
              timezone: '+07:00',
              format: '%d-%m-%Y %H:%M'
          }
     }
 }

edited Feb 25 '23 at 01:37

answered Feb 24 '23 at 16:22

Pradeepvina

90
1
7

1

This should be accepted as the most straightforward answer. – S. Pauk Mar 08 '23 at 08:54

score 2 · Answer 4 · answered Jul 25 '21 at 16:25

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

score 1 · Answer 5 · edited May 22 '23 at 18:31

1

You need to add the name of the timezone like so:

$project: {  
  releaseDate: {
    $dateToString: {
      date: "$releaseDate",
      timezone: "America/Sao_Paulo",
      format: "%d/%m/%Y %H:%M:%S"
    }
  }
}

edited May 22 '23 at 18:31

David Soroko

8,521
2
39
51

answered May 22 '23 at 16:19

Mateus Barros

11
2

Mongodb aggregate: convert date to another timezone

5 Answers5

Linked