在 mongodb 的本地时区聚合 [英] Aggregating in local timezone in mongodb

问题描述

我正在 mongodb 和 nodejs 中构建将在意大利使用的应用程序.意大利时区是 +02:00 .这意味着如果有人在 7 月 11 日上午 01:00 保存一些数据，那么它将保存为 7 月 10 日晚上 11:00，因为 mongo 以UTC 格式保存日期.我们需要显示日期明智的 tx 计数.所以我按日期查询分组.但它显示前一天的交易.对此应该采取什么解决方法.

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 }

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

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 月 11 日

It was actually 11 July in Italy when

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

发生但mongo存储日期为:

took place but mongo stored date as:

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

推荐答案

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

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

这给了你:

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

所以当你 $subtract 一个 BSON 日期来自另一个结果是自 unix 纪元以来的毫秒数.然后简单地通过添加"时区偏移"再次调整它，该时区偏移"要么是前小时的正数，要么是后数的负数，再次从时间值转换为有效的毫秒.

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.

四舍五入是一个简单的模 $mod 从一天中的毫秒数"中获取余数并将其删除以将调整后的日期四舍五入到当天.

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.

这里的结果数值很容易重新转换回日期，因为所有语言库Date"对象都将 epoch 中的毫秒(或秒)作为构造函数参数.

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

这篇关于在 mongodb 的本地时区聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！