在mongodb中以当地时区聚合

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.

我正在构建将在意大利使用的mongodb和nodejs中的应用程序。意大利时区是+02:00。这意味着如果任何人在7月11日凌晨01点保存一些数据,那么它将被保存为7月10日晚上11点,因为mongo以UTC格式保存日期。我们需要显示日期明智的tx计数。所以我按日期查询了。但它显示前一天的tx。应该解决这个问题。

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

它显示7月10日的2 txs和7月11日的1 txs。但是我们需要在7月11日显示2个tx,在7月10日显示1个tx。

It was actually 11 July in Italy when

实际上是7月11日在意大利的时候

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

took place but mongo stored date as:

发生但mongo存储日期为:

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

2 个解决方案

#1

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.

处理时区是一个“客户端”问题,因此您应该通过时区偏移修改“查询”时间,以便允许在UI中选择“本地”时间等。 UI显示也是如此,其中日期将在本地时间表示。

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

这同样适用于你的arggregation原则。只需按时区偏移调整即可。应用日期数学而不是使用日期聚合运算符:

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.

因此,当你从另一个中减去一个BSON日期时,结果是自unix epoch以来的毫秒数。然后再简单地通过“添加”“时区偏移”来调整此值,“时区偏移”可以是正向小时,也可以是后面的负数,再次从时间值转换为有效的毫秒数。

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.

然后,舍入是一个简单的模$ $ mod,用于从“一天中的毫秒数”中获取余数,并将其删除以将调整后的日期精简到当天。

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.

此处生成的数值很容易重新转换回日期,因为所有语言库“Date”对象都将epoch中的毫秒(或秒)作为构造函数参数。

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

#2

in mongo version 3.6 timezone has been added, mongo doc

在mongo版本3.6时区已添加,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

时区列表