CODEWITHSUNDEEP
mongodbaggregation-framework
Bazinga777
Bazinga777

Reputation: 5281

Mongodb get lastHour for each day

I have the following schema in mongodb, where the timestamp is the timestamp at an hourly level

{
    "day_chan1" : 54.464,
    "day_chan2" : 44.141,
    "day_chan3" : 44.89,
    "gatewayId" : "443719005AA3",
    "timestamp" : ISODate("2016-02-15T23:00:00.000Z"),
    "total_curr_chan" : 5.408,
    "total_day_chan" : 143.495,
    "type" : 1
}

I want to be able to query the last timestamp for the day for the last 7 days and 30 days. In order to do this, I am thinking of doing something like

    var d = new Date(); // today!

for(var i =1; i <= 7; i++) {

        var n = i; // go back n days!
        d.setDate(d.getDate() - n);
        d.setHours(23,0,0);

    var query =  {
        gatewayId: req.params.deviceId,
        timestamp: { $lt: new Date(d) }
    };

       db
        .find(query,function(resp) {
            //return the data here 
        });
    }

But this creates a problem of multiple callbacks and I want to know if there is an easier way of doing so using aggregates or some other method

Upvotes: 0

Views: 45

Answers (2)

Alex Blex
Alex Blex

Reputation: 37048

For arbitrary last hour it must be a bit more complex:

db.collection.aggregate([
    {$match:{
        timestamp:{$type: "date"}}
        // add date constraints here
    }, 
    {$project:{
        _id:1, 
        date:{"y":{$year:"$timestamp"}, "d":{$dayOfYear:"$timestamp"}},
        doc:"$$CURRENT"}
    },
    {$group:{
        _id:"$date", 
        maxtime: {$max:"$doc.timestamp"}, 
        doc:{$push:"$doc"}}
    },
    {$unwind:"$doc"},
    {$project:{
        latest: {$cmp: ["$maxtime", "$doc.timestamp"]}, 
        doc:"$doc"}
    },
    {$match:{"latest":0}}
])

With map-reduce it should be simpler, but may be slower.

Upvotes: 0

chridam
chridam

Reputation: 103425

Use the $hour operator within the $project operator to extract the hour part of the timestamp, then query with $match to filter documents that do not satisfy the given hour criteria:

var pipeline = [
    {
        "$project": {
            "day_chan1": 1,
            "day_chan2": 1,
            "day_chan3": 1,
            "gatewayId": 1,
            "timestamp": 1,
            "total_curr_chan": 1,
            "total_day_chan": 1,
            "type": 1,
            "hour": { "$hour": "$timestamp" }
        }
    },
    { "$match": { "hour": 23 } }
];

collection.aggregate(pipeline, function(err, result) {
    //return the data here 
    console.log(result);
});

Upvotes: 1

Related Questions