MongoDB聚合:将日期转换为另一个时区 [英] Mongodb aggregate: convert date to another timezone

问题描述

我用类似的东西保存我的交易:

I save my transaction with something like :

{code: "A", total: 250000, timestamp: ISODate("2016-01-20T23:57:05.771Z")},
{code: "B", total: 300000, timestamp: ISODate("2016-01-20T05:57:05.771Z")}

每笔交易的UTC时区下都有timestamp字段.由于我居住在雅加达(UTC + 7)时区，因此我需要在时间戳上添加7个小时，然后再进行汇总.这是我的mongo语法:

each of transaction has timestamp field under UTC timezone in it. Since I live in Jakarta (UTC+7) timezone, I need to add 7 hours to my timestamp before aggregation. Here's my mongo syntax:

db.transaction.aggregate(
[
 {
   $project:
     {
       year: { $year: "$timestamp" },
       month: { $month: "$timestamp" },
       day: { $dayOfMonth: "$timestamp" }
     }
 }
])

它返回:

    {
        "_id" : ObjectId("56a01ed143f2fd071793d63b"),
        "year" : 2016,
        "month" : 1,
        "day" : 20
    },
    {
        "_id" : ObjectId("56a01ed143f2fd071793d63b"),
        "year" : 2016,
        "month" : 1,
        "day" : 20
    }

这是错误的，因为第一笔交易(代码A)发生在1月21日，但是由于它已转换为UTC(-7小时)，因此变成了ISODate("2016-01-20T23:57:05.771Z")

which is wrong since the first transaction (code A), is happened at 21st January, but since it was converted to UTC (-7 Hours), it became ISODate("2016-01-20T23:57:05.771Z")

注意:我知道关于此处存在相同的问题，这是到目前为止我已经尝试过的方法:

Note: I'm aware about the same problem over here, here's what I've been tried so far:

db.transaction.aggregate(
[
 {$project: {"timestamp": {$add: [7 * 60 * 60 * 1000]}}},
 {
   $project:
     {
       year: { $year: "$timestamp" },
       month: { $month: "$timestamp" },
       day: { $dayOfMonth: "$timestamp" }
     }
 }
])

，但它返回can't convert from BSON type NumberDouble to Date错误. 有什么建议吗?

but it returns can't convert from BSON type NumberDouble to Date error. Any suggestions?

推荐答案

您需要将时间戳记添加到7 * 60 * 60 * 1000，您也可以在一个

You need to add the "timestamp" to 7 * 60 * 60 * 1000 also you can do this in one $project stage.

db.collection.aggregate([
    { "$project": {
        "year": { "$year": { "$add": [ "$timestamp", 7 * 60 * 60 * 1000 ] } }, 
        "month": { "$month": { "$add": [ "$timestamp", 7 * 60 * 60 * 1000 ] } }, 
        "day": { "$dayOfMonth": { "$add": [ "$timestamp", 7 * 60 * 60 * 1000 ] } } 
    } }
])

哪个返回:

{
        "_id" : ObjectId("56a07f8f8448ed9fd2359365"),
        "year" : 2016,
        "month" : 1,
        "day" : 21
}
{
        "_id" : ObjectId("56a07f8f8448ed9fd2359366"),
        "year" : 2016,
        "month" : 1,
        "day" : 20
}

这篇关于MongoDB聚合:将日期转换为另一个时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！