CODEWITHSUNDEEP
javamongodbtimezone
cool cool
cool cool

Reputation: 67

Group by day and get last seven days data with respect to time zone in MongoDB

I have lot of document that look like this.

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

My backend language is java. I use _id field to filter data by date.

I have a java method which give me Object_id with respect to timezone.

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 could be something like .

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

Now i would like to add some charts on my application dashboard. so i need last 7 days data like this.

{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}

Since lot of users use different time zone, I really dont have any idea how to do that.

help please.

Upvotes: 1

Views: 440

Answers (1)

s7vr
s7vr

Reputation: 75964

You just need to fetch the matching documents, group by date with timezone and sum the scores and output the document.

Fetch and format results

//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)); 
}

Helper Methods

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");
}

Upvotes: 1

Related Questions