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

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

CodePudding user response：

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