How to group date quarterly wise?

Question

I have documents which contain a date and I'm wondering how to group them according to quarterly basis?

My schema is:

var ekgsanswermodel = new mongoose.Schema({
    userId: {type: Schema.Types.ObjectId},
    topicId : {type: Schema.Types.ObjectId},
    ekgId : {type: Schema.Types.ObjectId},
    answerSubmitted :{type: Number},
    dateAttempted : { type: Date},
    title : {type: String},
    submissionSessionId : {type: String}  
});

1st quarter contains months 1, 2, 3. 2nd quarter contains months 4, 5, 6 and so on up-to 4th quarter.

My final result should be:

 "result" : [ 
   {
     _id: {
        quater:
     },
     _id: {
        quater:
     },
    _id: {
        quater:
     },
     _id: {
        quater:
     }
  }

have you tried using MongoDB's mapreduce? http://docs.mongodb.org/manual/core/map-reduce/ I think you can map your months by rounded MonthNumber/4. Something like emit(this.number/4, this.number). And in REDUCE part, you group all the months. — AzaFromKaza, Feb 03 '15 at 04:55
I have not tried mapreduce. can give me a example. i added schema also above. — Rishabh Garg, Feb 03 '15 at 06:02
Nevermind my solution @grishabh, the one below is better. Mongodb mapreduce does not allow you to have an array as a result of reduce operation. Reference: http://stackoverflow.com/questions/8175015/mongodb-mapreduce-reduce-multiple-not-supported-yet — AzaFromKaza, Feb 03 '15 at 07:09

score 25 · Accepted Answer · edited Oct 21 '20 at 10:59

You could make use of the $cond operator to check if:

The $month is <= 3, project a field named quarter with value as "one".
The $month is <= 6, project a field named quarter with value as "two".
The $month is <= 9, project a field named quarter with value as "three".
else the value of the field quarter would be "fourth".
Then $group by the quarter field.

Code:

db.collection.aggregate([
  {
    $project: {
      date: 1,
      quarter: {
        $cond: [
          { $lte: [{ $month: "$date" }, 3] },
          "first",
          {
            $cond: [
              { $lte: [{ $month: "$date" }, 6] },
              "second",
              {
                $cond: [{ $lte: [{ $month: "$date" }, 9] }, "third", "fourth"],
              },
            ],
          },
        ],
      },
    },
  },
  { $group: { _id: { quarter: "$quarter" }, results: { $push: "$date" } } },
]);

Specific to your schema:

db.collection.aggregate([
  {
    $project: {
      dateAttempted: 1,
      userId: 1,
      topicId: 1,
      ekgId: 1,
      title: 1,
      quarter: {
        $cond: [
          { $lte: [{ $month: "$dateAttempted" }, 3] },
          "first",
          {
            $cond: [
              { $lte: [{ $month: "$dateAttempted" }, 6] },
              "second",
              {
                $cond: [
                  { $lte: [{ $month: "$dateAttempted" }, 9] },
                  "third",
                  "fourth",
                ],
              },
            ],
          },
        ],
      },
    },
  },
  { $group: { _id: { quarter: "$quarter" }, results: { $push: "$$ROOT" } } },
]);

thanx @BatScream, Please can you make this query in spring-data-mongodb. — user1503117, Sep 20 '15 at 16:26

score 5 · Answer 2 · answered Jan 18 '19 at 15:43

5

You could use following to group documents quarterly.

{
    $project : {
        dateAttempted : 1,
        dateQuarter: {
            $trunc : {$add: [{$divide: [{$subtract: [{$month: 
            "$dateAttempted"}, 1]}, 3]}, 1]}
        }
    }
}

answered Jan 18 '19 at 15:43

Tofeeq

2,523
1
23
20

I like the short if possible. Thanks. – newman Jan 11 '22 at 17:33

score 3 · Answer 3 · answered Dec 04 '21 at 10:03

Starting in Mongo 5, it's a perfect use case for the new $dateTrunc aggregation operator:

// { date: ISODate("2012-10-11") }
// { date: ISODate("2013-02-27") }
// { date: ISODate("2013-01-12") }
// { date: ISODate("2013-03-11") }
// { date: ISODate("2013-07-14") }
db.collection.aggregate([
  { $group: {
    _id: { $dateTrunc: { date: "$date", unit: "quarter" } },
    total: { $count: {} }
  }}
])
// { _id: ISODate("2012-10-01"), total: 1 }
// { _id: ISODate("2013-01-01"), total: 3 }
// { _id: ISODate("2013-07-01"), total: 1 }

$dateTrunc truncates your dates at the beginning of their quarter (the truncation unit). It's kind of a modulo on dates per quarter.

Quarters in the output will be defined by their first day (Q3 2013 will be 2013-07-01). And you can always adapt it using $dateToString projection for instance.

How to group date quarterly wise?

3 Answers3

Linked