如何在不同时区按年月日汇总 [英] How to aggregate by year-month-day on a different timezone
问题描述
我有一个 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.
这篇关于如何在不同时区按年月日汇总的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!