mongodbaggregates

How to get reading of collection for previous day, i.e., yesterday using aggregate in mongodb


I have data of various timestamps and I want to create an aggregate pipeline to get sum of a column having yesterdays date. And I don't want to hardcode current date to get yesterday date. Please suggest how to do it as I am new to Mongodb

Edit : collection name - consumption_data
documents -

1. id :101,  timestamp : 2022-09-10T22:00:00.000+00:00, consumption: 199
2. id :106,  timestamp : 2022-09-10T07:00:00.000+00:00, consumption: 201
3. id :108,  timestamp : 2022-09-11T12:00:00.000+00:00, consumption: 77
4. id :109,  timestamp : 2022-09-11T08:00:00.000+00:00, consumption: 773

If today is 2022-09-11 the I want consumption of yesterday(2022-09-10) without hardcoding the dates


Solution

  • Try this one:

    db.consumption_data.aggregate([
       {
          $match: {
             $expr: {
                $gt: ["$timestamp", {
                   $dateSubtract: {
                      startDate: "$$NOW",
                      unit: "day",
                      amount: 1
                   }
                }]
             }
          }
       },
       { $group: { _id: null, consumption: { $sum: "$consumption" } } }
    ])
    

    Consider the use of $dateTrunc, (i.e. { $dateTrunc: { date: "$$NOW", unit: "day" } }) otherwise it will go back exactly 24 hours from the current time

    db.consumption_data.aggregate([
       {
          $match: {
             $expr: {
                $and: [
                   {
                      $gte: ["$timestamp",
                         {
                            $dateSubtract: {
                               startDate: { $dateTrunc: { date: "$$NOW", unit: "day" } },
                               unit: "day",
                               amount: 1
                            }
                         }
                      ]
                   },
                   {
                      $lt: ["$timestamp",
                         { $dateTrunc: { date: "$$NOW", unit: "day" } }
                      ]
                   }
                ]
             }
          }
       },
       { $group: { _id: null, consumption: { $sum: "$consumption" } } }
    ])