node.js - 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