Question 116692 in Mongodb Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is a working solution:

    db.totalConsumption_data.aggregate([
  {
    '$project': {
      'month': {
        '$month': '$timestamp'
      }, 
      'year': {
        '$year': '$timestamp'
      }, 
      'day': {
        '$dayOfMonth': '$timestamp'
      }, 
      'timestamp': 1, 
      'consumption': 1
    }
  }, {
    '$match': {
      'month': new Date().getMonth() +1, 
      'day': new Date().getDate()-1, 
      'year': new Date().getFullYear()
    }
  }, {
    '$group': {
      '_id': null, 
      'total': {
        '$sum': '$consumption'
      }
    }
  }
]

  2. 0

    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" } } }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search