CODEWITHSUNDEEP
javascriptnode.jsmongodb
David Ramírez
David Ramírez

Reputation: 71

How to get data average per day on Mongodb?

I want to know if there is a way to get the average of data per day using an aggregation. Here is a sample of my data: 

[{"eApa":70519217,"pReacTotal":-111920,"pAparTotal":169404,"eCap":44641702,"eInd":478186,"vL1":127.646484375,"eActImp":41766944,"time":1564763657341},{"eApa":70520927,"pReacTotal":-123544,"pAparTotal":159832,"eCap":44642873,"eInd":478186,"vL1":127.9453125,"eActImp":41768133,"time":1564763694724},{"eApa":70522183,"pReacTotal":-139188,"pAparTotal":176512,"eCap":44643864,"eInd":478186,"vL1":127.771484375,"eActImp":41768890,"time":1564763716707},{"eApa":70522513,"pReacTotal":-155828,"pAparTotal":196372,"eCap":44644128,"eInd":478186,"vL1":127.865234375,"eActImp":41769086,"time":1564763722905},{"eApa":70524589,"pReacTotal":-165072,"pAparTotal":226156,"eCap":44645715,"eInd":478186,"vL1":127.486328125,"eActImp":41770404,"time":1564763760126},{"eApa":70526081,"pReacTotal":-168376,"pAparTotal":249252,"eCap":44646779,"eInd":478186,"vL1":127.142578125,"eActImp":41771487,"time":1564763782323},

As you can see the time field is long type, but i don't know how to get the average of the data that corresponds to one day. I mean, the data on the example is for August 2nd, there is more data that is for August 3rd and so until the current date. And i want to get the average only for each day for the other fields, for example, started on August 2nd at 00:00:00 until 23:59:59.

Here is the code on Nodejs that brings the data: 

TestC.aggregate([
            {
                '$match': {
                    'time': {
                        '$gte': new Date(Date.now() - (/* 86400 */ 2332800 * 1000))
                    }
                }
            }, {
                '$project': {
                    '_id': 0,
                    'pAparTotal': 1,
                    'pReacTotal': 1,
                    'eApa': 1,
                    'eInd': 1,
                    'eCap': 1,
                    'eActImp': 1,
                    'time': {
                        '$toLong': '$time'
                    }
                }
            }, {
                '$sort': {
                    'time': 1
                }
            }
        ]).exec((ex, data) => {
            if (ex) return res.status(500).send({ message: "Error tryng to get the data" });

            if (!data) return res.status(400).send({ message: No data." });

            if (data) return res.status(200).send(data)
        });

Thanks!

Upvotes: 1

Views: 507

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

Please try this :

TestC.aggregate([
    {
        "$group": {
            "_id": {
                "$dateToString": {
                    "format": "%Y-%m-%d", "date": {
                        "$toDate": "$time"
                    }
                }
            },
            "eApa": { "$avg": '$eApa' },
            "pReacTotal": { "$avg": '$pReacTotal' },
            "pAparTotal": { "$avg": '$pAparTotal' },
            "eCap": { "$avg": '$eCap' },
            "eInd": { "$avg": '$eInd' },
            "vL1": { "$avg": '$vL1' },
            "eActImp": { "$avg": '$eActImp' }
        }
    }
])

Output :

/* 1 */
{
    "_id" : "2019-08-02",
    "eApa" : 70522585.0,
    "pReacTotal" : -143988.0,
    "pAparTotal" : 196254.666666667,
    "eCap" : 44644176.8333333,
    "eInd" : 478186.0,
    "vL1" : 127.642903645833,
    "eActImp" : 41769157.3333333
}

Upvotes: 1

Related Questions