Mongodb aggregate timezone 问题

来源：互联网发布：北京海洋馆客流量数据编辑：程序博客网时间：2024/05/19 03:46

在用aggregate 进行数据统计处理的时候，由于系统默认使用Mongodb的UTC时间，与我们时区差了8小时，会出现结果误差。为了获得正确的结果，在进行

aggregate 处理时需要在原来的基础上做加8小时处理。

测试数据如下

数据记录

下面我们来计算下shop_id 等于57300412且时间大于’2014-04-01T00:02:00Z’ 按天排序的统计结果。shell表达式如下

1
2
3
4
5
6
7
8
9
10
11
12
13
//经过时差处理
db.test.aggregate({$match:{'_id.shop_id':57300412}},
{$group:{
_id:{
year:{$year:{$add:['$_id.date',28800000]}},
month:{$month:{$add:['$_id.date',28800000]}},
day:{$dayOfMonth:{$add:['$_id.date',28800000]}
}},
amount:{$sum:'$value.amount'},
count:{$sum:'$value.count'},
cost:{$sum:'$value.cost'}}},
{$project:{_id:1,count:1,amount:1,cost:1}}
)

1
2
3
4
5
6
7
8
9
10
11
12
13
//没有经过时差处理
db.test.aggregate({$match:{'_id.shop_id':57300412}},
{$group:{
_id:{
year:{$year:'$_id.date'},
month:{$month:'$_id.date'},
day:{$dayOfMonth:'$_id.date'}
},
amount:{$sum:'$value.amount'},
count:{$sum:'$value.count'},
cost:{$sum:'$value.cost'}}},
{$project:{_id:1,count:1,amount:1,cost:1}}
)

对比结果如下

结果差值
注意上图增加时差和未增加时差的结果是不一样的。

说明：

1
2
$dayOfYear:计算日期的该年第几天，返回1到366
$add:['$_id.date',28800000]: 时区数据校准，8小时换算成毫秒数为8*60*60*1000= 28800000.

相应的Java代码片段如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DBObject match = new BasicDBObject("$match", new BasicDBObject("_id.shop_id", Integer.parseInt(shopAmount.getShopId())).
                append("_id.date", new BasicDBObject("$gte", startDate).append("$lte", endDate)));
 
BasicDBList dbList = new BasicDBList();
dbList.add("$_id.date");
dbList.add(28800000);//解决timezone8小时时差
 
Map<String, Object> dbObjIdMap = new HashMap<String, Object>();
dbObjIdMap.put("year", new BasicDBObject("$year",new BasicDBObject("$add",dbList)));
dbObjIdMap.put("month", new BasicDBObject("$month",new BasicDBObject("$add",dbList)));
dbObjIdMap.put("dayOfMonth", new BasicDBObject("$dayOfMonth",new BasicDBObject("$add",dbList)));
DBObject groupFields = new BasicDBObject( "_id", new BasicDBObject(dbObjIdMap));
 
groupFields.put("amount", new BasicDBObject("$sum","$value.amount"));
groupFields.put("count", new BasicDBObject("$sum","$value.count"));
groupFields.put("cost", new BasicDBObject("$sum","$value.cost"));
DBObject group = new BasicDBObject("$group", groupFields);
 
DBObject fields = new BasicDBObject("_id",1);
fields.put("cost",1);
fields.put("amount",1);
fields.put("count",1);
DBObject project = new BasicDBObject("$project", fields );
DBObject sort = new BasicDBObject("$sort", new BasicDBObject("_id",-1));
/* 查看Group结果 */
AggregationOutput output = collection.aggregate(match, group,project,sort); // 执行 aggregation命令
System.out.println(output.getCommandResult());
Collection<Object> c = output.getCommandResult().values();
Object o[] = c.toArray();
BasicDBList resultList = (BasicDBList) o[1];

0 0