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