node.js - Aggregating in local timezone in mongodb -

i building application in mongodb , nodejs used in italy . italy timezone +02:00 . means if 1 saving data @ 01:am of 11 july saved 11:00 pm of 10 july mongo saves date in utc. need show date wise tx count. made group query on date. shows tx in previous day. should workaround 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 , 1 in 11 july . need show 2 txs 11 july , 1 tx 10 july.

it 11 july in italy when

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

took place mongo stored date as:

isodate("2015-07-10t23:00:00z") 

dealing timezones "client" issue, shoud modifying "query" times timezone offset in order allow "local" time selection in ui , forth. same goes ui display dates represented in local time.

and same applies arggregation principle. adjust timezone offset. appply date math instead of using 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 $subtract 1 bson date result number of milliseconds since unix epoch. adjust again "adding" "timezone offset" being either possitive forward hours or negative behind, again converted valid millseconds time value.

the rounding simple modulo $mod remainder "number of milliseconds in day" , remove round out adjusted date current day only.

the resulting numeric values here re-cast dates since language library "date" objects take milliseconds ( or seconds ) epoch constructor argument.

so again, modifying data response present "locale" of "client" , not channging how data stored. if want true locality in application apply modifications timezone offsets everywhere, presented above.

--

actually can create date in aggregation framework itself, little more date math. add epoch date 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 }     }} ])