{
metadata:{
dat:jkjcsvbdskjcbdskjcbdac,
meterId:kahcvajc
}
activeEnergy:1111,
actualtime:1689827191000
}
The document is something like that, I am facing problem only with activeEnergy so I want to focus on that only. Below I have written the code, in the first group object I have divided by year, month, day but in actual code it is dynamic, if the payload from frontend I receive is month and week then I group accordingly, but if I receive day in payload then I group by hour, I calculate the max and min energy of that hour and then sum for all hour, as activeEnergy is continuous, but the problem is that I am not getting data at every second so it is possible that I get the first data at 10;25am and last data at 10:45am therefore by taking max and min I only calculate for that 20 min and miss on all the remaining time data, ideally what I should do is max of this hour by max of previous hour.
That becomes a problem because data is not grouped like that by me, how to solve the problem?
db.ts_events.aggregate([
{
$project: {
"y":{"$year": {$toDate: "$actualtime"}},
"m":{"$month": {$toDate: "$actualtime"}},
"d":{"$dayOfMonth": {$toDate: "$actualtime"}},
"h":{"$hour": {$toDate: "$actualtime"}},
"activeEnergy": 1,
"metadata.meterId": 1,
"activePower": 1,
"actualtime": 1,
"powerFactor": 1,
"metadata.dat": 1
}
},
{
$match: {
"metadata.dat": "62f0f3459731692a5eab5ad6/south0tpbit/tamilnadu5dvs8w/chennaidzc2yd/kknagarj4ffzo",
"actualtime": {
$gte: 1656613800000, $lte: 1659292199999,
},
// "metadata.device":"ObjectId(62f0f9b5f757672222282d9)" how to check using object id?,
"metadata.meterId": "911615402222257_2",
}
},
{
$group: {
_id: {
date: {
year: "$y",
month: "$m",
day: "$d",
// hour: "$h",
},
meter: "$metadata.meterId",
},
maxValue: {
$max: "$activeEnergy"
},
minValue: {
$min: "$activeEnergy"
},
averageActivePowerOfDay: { $avg: "$activePower" },
averagePowerFactorOfDay: { $avg: "$powerFactor" },
}
},
{
$addFields: {
differnce: {
$subtract: [
"$maxValue",
"$minValue"
]
},
}
},
//
//
//
{
$group: {
_id: null, res: {
$push: '$$ROOT'
}, differnceSum: {
$sum: '$differnce'
},
averageActivePowerOverThePeriod: {
$avg: "$averageActivePowerOfDay"
},
averagePowerFactorOverThePeriod: {
$avg: "$averagePowerFactorOfDay"
}
}
}
])