java - 按天分组并获取 MongoDB 中时区的最近 7 天数据

Question

我有很多类似这样的文档。

{
  "_id" : ObjectId("5bcf7d670a31a41b382823e2"), 
  "score" : 75
}

我的后端语言是java。我使用 _id 字段按日期过滤数据。

我有一个java方法，它给我关于时区的Object_id。

public static ObjectId getObjectId(String date, String fromTimeZone) {
  SimpleDateFormat formatterFrom = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  formatterFrom.setTimeZone(TimeZone.getTimeZone(fromTimeZone));
  return new ObjectId(Long.toHexString(formatterFrom.parse(date).getTime() / 1000L) + "0000000000000000");
}

fromTimeZone 可能类似于 .

GMT+08:00
UTC
Africa/Algiers
Europe/London etc.

现在我想在我的应用程序仪表板上添加一些图表。所以我需要像这样的过去 7 天的数据。

{date: Nov-01, score:75}
{date: Nov-02, score:75}
{date: Nov-03, score:75}
{date: Nov-04, score:75}
{date: Nov-05, score:75}
{date: Nov-06, score:75}
{date: Nov-07, score:75}

由于很多用户使用不同的时区，我真的不知道如何做到这一点。

请帮忙。

Answer 1

您只需要获取匹配的文档，按日期和时区分组并对分数求和并输出文档。

获取结果并设置格式

//Create Variables
String endDt = "2018-11-08 01:02:03";
String startDt = "2018-11-01 01:02:03";
String timeZone = "GMT+08:00";

//Query Filter
Bson query = Aggregates.match(Filters.and(
     Filters.lte("_id", getObjectId(endDt,timeZone)),
     Filters.gte("_id", getObjectId(startDt,timeZone ))
));

//Objectid to datetime expression
Document toDate = new Document("$toDate", "$_id");
Bson objectIdToDate = Aggregates.projection(Projections.fields(
     Projections.excludeId(),
     Projections.include("score"),
     Projections.computed("date", toDate)
));

//Date expression with timezone
Document dateExpression = Document.parse(
  "{'$dateFromParts':{
   'year':{'$year':{'date':'$date','timezone:'"+ timeZone +"}},
   'month':{'$month':{'date':'$date','timezone':"+ timeZone +"}},
   'day':{'$dayOfMonth':{'date':'$date','timezone':"+ timeZone +"}}
   }}"
);

//Group by Date
Bson group = Aggregates.group(new Document("$_id", dateExpression), Accumlators.sum("score", "$score"));

//final output
Bson fields = Aggregates.projection(Projections.fields(
     Projections.excludeId(),
     Projections.include("score"),
     Projections.computed("date", "$_id")
));

//Fetch matching records
AggregateIterable<Document> iterable = collection.aggregate(Arrays.asList(query,objectIdToDate,group,fields));

//Format results
for(Document document:iterable) {
    document.put("date", formatDateToMonthDay(date)); 
}

辅助方法

public static String formatDateToMonthDay(Date date) {
    DateTimeFormattter monthDayFormatter = DateTimeFormatter.ofPattern("MMM-dd");
    Instant instant = date.toInstant();
    return instant.format(monthDayFormatter); 
}

public ObjectId getObjectId(String date, String fromTimeZone) {
    DateTimeFormattter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    LocalDateTime localDateTime = LocalDateTime.parse(date,formatter);
    Instant instant = LocalDateTime.ofInstant(instant, ZoneId.of(fromTimeZone)).toInstant();
    return new ObjectId(Long.toHexString(instant.getEpochSecond()) + "0000000000000000");
}