Mongodb aggregate timezone 问题

2019独角兽企业重金招聘Python工程师标准>>>

在用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);//解决timezone 8小时时差   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];

转载于:https://my.oschina.net/u/1458864/blog/1506151