Aggregating in local timezone in mongodb

Question

I am building application in mongodb and nodejs that will be used in Italy . Italy timezone is +02:00 . This means if any one saving some data at 01:am of 11 July then it will be saved as 11:00 pm of 10 July as mongo saves date in UTC. We need to show date wise tx count. So I made group by query on date. But it shows that tx in previous day. What should be workaround for this.

> db.txs.insert({txid:"1",date : new Date("2015-07-11T01:00:00+02:00")})

> db.txs.insert({txid:"2",date : new Date("2015-07-11T05:00:00+02:00")})

> db.txs.insert({txid:"3",date : new Date("2015-07-10T21:00:00+02:00")})

> db.txs.find().pretty()

{
        "_id" : ObjectId("55a0a55499c6740f3dfe14e4"),
        "txid" : "1",
        "date" : ISODate("2015-07-10T23:00:00Z")
}
{
        "_id" : ObjectId("55a0a55599c6740f3dfe14e5"),
        "txid" : "2",
        "date" : ISODate("2015-07-11T03:00:00Z")
}
{
        "_id" : ObjectId("55a0a55699c6740f3dfe14e6"),
        "txid" : "3",
        "date" : ISODate("2015-07-10T19:00:00Z")
}

> db.txs.aggregate([
     { $group:{
         _id: { 
             day:{$dayOfMonth:"$date"}, 
             month:{$month:"$date"},
             year:{$year:"$date"} 
         },
         count:{$sum:1}
     }}
  ])

  { "_id" : { "day" : 11, "month" : 7, "year" : 2015 }, "count" : 1 }
  { "_id" : { "day" : 10, "month" : 7, "year" : 2015 }, "count" : 2 }

It shows 2 txs in 10th of July and 1 in 11 July . But we need to show 2 txs for 11 july and 1 tx for 10 July.

It was actually 11 July in Italy when

db.txs.insert({txid:"1",date : new Date("2015-07-11T01:00:00+02:00")})

took place but mongo stored date as:

ISODate("2015-07-10T23:00:00Z")

Answer 1

in mongo version 3.6 timezone has been added, mongo doc

expression to extract date part with timezone is

{ date: <dateExpression>, timezone: <tzExpression> }

we can either specify the timezone or offset while getting the date parts

pipeline

> db.txs.aggregate([
...     { $group:{
...         _id: { 
...             day: {$dayOfMonth: {date :"$date", timezone : "Europe/Rome"}}, // timezone
...             month: {$month: {date : "$date", timezone : "+02:00"}}, //offset
...             year: {$year: {date : "$date", timezone : "+02:00"}} //offset
...         },
...         count:{$sum:1}
...     }}
... ])

result

{ "_id" : { "day" : 10, "month" : 7, "year" : 2015 }, "count" : 1 }
{ "_id" : { "day" : 11, "month" : 7, "year" : 2015 }, "count" : 2 }
> 

list of timezone

Answer 2

Dealing with timezones is a "client" issue, so you shoud be modifying "query" times by the timezone offset in order to allow "local" time selection in UI and so forth. The same goes for UI display where the dates are to be represented in the local time.

And the same applies to your arggregation principle. Just adjust by the timezone offset. Appply date math instead of using the date aggregation operators:

var tzOffset = 2;

db.txs.aggregate([
    { "$group": {
        "_id": { 
            "$subtract": [
                { "$add": [ 
                    { "$subtract": [ "$date", new Date("1970-01-01") ] },
                    tzOffset * 1000 * 60 * 60
                ]},
                { "$mod": [
                    { "$add": [ 
                        { "$subtract": [ "$date", new Date("1970-01-01") ] },
                        tzOffset * 1000 * 60 * 60
                    ]},
                    1000 * 60 * 60 * 24
                ]}
            ]
        },
        "count": { "$sum": 1 }
    }}
]).forEach(function(doc){ 
    printjson({ "_id": new Date(doc._id), "count": doc.count }) 
});

Which gives you:

{ "_id" : ISODate("2015-07-10T00:00:00Z"), "count" : 1 }
{ "_id" : ISODate("2015-07-11T00:00:00Z"), "count" : 2 }

So when you $subtract one BSON date from another the result is the number of milliseconds since unix epoch. Simply then adjust this again by "adding" the "timezone offset" being either possitive for forward hours or negative for behind, again converted to valid millseconds from the time value.

The rounding then is a simple modulo $mod to get the remainder from the "number of milliseconds in a day" and remove that to round out the adjusted date to the current day only.

The resulting numeric values here are easily re-cast back into dates since all language library "Date" objects take the milliseconds ( or seconds ) from epoch as a constructor argument.

So again, this is all about modifying the data response to present from the "locale" of your "client" and not about channging how the data is stored. If you want true locality in your application then you apply modifications for timezone offsets everywhere, just as is presented above.

--

Actually you can just create the date in the aggregation framework itself, with a little more date math. Simply add the epoch date back to the converted date:

db.txs.aggregate([
    { "$group": {
        "_id": { 
            "$add": [
                { "$subtract": [
                    { "$add": [ 
                        { "$subtract": [ "$date", new Date(0) ] },
                        tzOffset * 1000 * 60 * 60
                    ]},
                    { "$mod": [
                        { "$add": [ 
                            { "$subtract": [ "$date", new Date(0) ] },
                            tzOffset * 1000 * 60 * 60
                        ]},
                        1000 * 60 * 60 * 24
                    ]}
                ]},
                new Date(0);
            ]
        },
        "count": { "$sum": 1 }
    }}
])