Mongodb aggregate timezone 问题

在用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 ];



©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页