如何在不同时区按年-月-日汇总

问题描述

我有一个MongoDB，它以UTC存储日期对象.好吧，我想按年，月日在不同的时区(CET)中进行汇总.

I have a MongoDB whom store the date objects in UTC. Well, I want to perform aggregation by year,month day in a different timezone (CET).

这样做，对于UTC来说效果很好:

doing this, works fine for UTC:

    BasicDBObject group_id = new BasicDBObject("_id", new BasicDBObject("year", new BasicDBObject("$year", "$tDate")).
                append("month", new BasicDBObject("$month", "$tDate")).
                append("day", new BasicDBObject("$dayOfMonth", "$tDate")).
                append("customer", "$customer"));

    BasicDBObject groupFields = group_id.
            append("eventCnt", new BasicDBObject("$sum", "$eventCnt"));

    BasicDBObject group = new BasicDBObject("$group", groupFields);

或者，如果您使用命令行(未经测试，我仅测试了Java版本):

or, if you use the command line (not tested, I only tested the java version):

{
    $group: {
        _id: {
            "year": {
                "$year", "$tDate"
            },
            "month": {
                "$month", "$tDate"
            },
            "day": {
                "$dayOfMonth", "$tDate"
            },
            "customer": "$customer"
        },
        "eventCount": {
            "$sum": "$eventCount"
        }
    }
}

如何在汇总框架中将这些日期转换为CET?

How do I convert these dates into CET inside the aggregation framework?

例如，"2013-09-16 23:45:00 UTC"是"2013-09-17 00:45:00 CET"，这是另一天.

For example '2013-09-16 23:45:00 UTC' is '2013-09-17 00:45:00 CET', this is a different day.

推荐答案

我不是CET及其与UTC的关系方面的专家，但是以下代码(对于Shell)应该进行适当的转换(添加一个小时)到MongoDB日期类型:

I'm not an expert on CET and its relation to UTC, but the following code (for the shell) should do a proper conversion (adding an hour) to a MongoDB date type:

db.dates.aggregate(
  {$project: {"tDate":{$add: ["$tDate", 60*60*1000]}, "eventCount":1, "customer":1}}
)

如果您在管道的其余部分之前运行该项目命令，则结果应在CET中.

If you run that project command before the rest of your pipeline, the results should be in CET.

这篇关于如何在不同时区按年-月-日汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！