CODEWITHSUNDEEP
javascriptmongodb
user3142695
user3142695

Reputation: 17322

aggregate data by date interval

This is how I'm doing an aggregation query to get the average time between start and end time (both in the format ISODate("2020-02-24T13:08:00.123Z")).

But I need to split the result data into two groups as I need to get the average data for all datasets with start time 04/2019 - 09/2020 and the second group all data with start time 10/2019 - 04/2020. I don't get it how to group by these two interval for an ISODate value

const data = await Data.aggregate([
    {
    $match: {
        type: { $exists: true },
        statsIgnore: { $exists: false }
    }
    },
    {
    $group: {
        _id: '$type',
        Datasets: { $sum: 1 },
        Average: {
        $avg: {
            $divide: [
            { $subtract: ['$timeEnd', '$timeStart'] },
            60000
            ]
        }
        }
    }
    }
]).toArray()

My data structure

[
    {
        _id: ObjectId("5d9242cf863feb0b8d70d12e"),
        timeStart: ISODate("2020-02-24T13:08:00.123Z"),
        timeEnd: ISODate("2020-02-24T13:18:00.123Z"),
        type: 'type1'
    },
    {
        _id: ObjectId("5d9242cf863feb0b8d70d12f"),
        timeStart: ISODate("2019-08-29T17:05:00.123Z"),
        timeEnd: ISODate("2019-08-29T17:25:00.123Z"),
        type: 'type1'
    }
]

In this simple data example there is only one type with a single dataset for summer and a single dataset for winter interval.

So the result should be 10 minutes average for winter and 20 minutes average for summer (for type1 group).

Upvotes: 1

Views: 147

Answers (2)

Valijon
Valijon

Reputation: 13093

Check if this meets your requirements:

db.data.aggregate([
  {
    $match: {
      type: {
        $exists: true
      },
      statsIgnore: {
        $exists: false
      }
    }
  },
  {
    $group: {
      _id: {
        type: "$type",
        season: {
          $arrayElemAt: [
            [
              "None",
              "Winter",
              "Winter",
              "Spring",
              "Spring",
              "Spring",
              "Summer",
              "Summer",
              "Summer",
              "Autumn",
              "Autumn",
              "Autumn",
              "Winter"
            ],
            {
              $month: "$timeStart"
            }
          ]
        }
      },
      Datasets: {
        $sum: 1
      },
      Average: {
        $avg: {
          $divide: [
            {
              $subtract: [
                "$timeEnd",
                "$timeStart"
              ]
            },
            60000
          ]
        }
      }
    }
  }
])

MongoPlayground

Upvotes: 1

Lauren Schaefer
Lauren Schaefer

Reputation: 706

The approach I took is to check that the timeStart is in the range you're looking for in the initial $match stage. Then I added an $addFields stage that checks if the season is summer based on the start date. Then I grouped by my new summer field. 

[
{$match: {
  type: {
    $exists: true
  },
  statsIgnore: {
    $exists: false
  }, 
  timeStart: {
    $gte: ISODate("2019-04-01T00:00:00Z"),
    $lt: ISODate("2020-04-01T00:00:00Z")
  }
}}, 

{$addFields: {
  summer: { $lt: ["$timeStart", ISODate("2019-09-01T00:00:00Z")]}
}}, 

{$group: {
  _id: "$summer",
  Average: {
        $avg: {
            $divide: [
            { $subtract: ['$timeEnd', '$timeStart'] },
            60000
            ]
        }
        }
}}]

Upvotes: 1

Related Questions