CODEWITHSUNDEEP
mongodb
Sabareesh Gunasekaran
Sabareesh Gunasekaran

Reputation: 41

Group By Hour using UNIX time stamp in mongodb

I required records with the output of gender, count, and updated hour for two days.

db.FaceData.aggregate([ {$match: { 'Timestamp' : { $gte : 1448121600000, $lt : 1448294399000 }, 'DID' : "ABFR001" }},  {$group: { _id: {'Gen': '$Gen'},  count : { $sum : 1 } }}  ]);
output:
 ------
{ "_id" : { "Gen" : 1 }, "count" : 3055 }
{ "_id" : { "Gen" : 0 }, "count" : 2866 }

In the above output I have to group by hour for two days, For Example, Every hour I need Gender, Count for 2days.

Timestamp is in millisecond.

Upvotes: 1

Views: 1186

Answers (2)

Sabareesh Gunasekaran
Sabareesh Gunasekaran

Reputation: 41

{"$match": { 
        "Timestamp" : { $gte : 1448121600000, $lt : 1448294399000 }, 
        "DID" : "ABFR001" 
    }} ,
{ "$group" :  {
"_id" : {

    "$divide" : [{ "$subtract" : [{"$divide" : ["$Timestamp", 1000]}, { "$mod" : [{"$divide" : ["$Tstmp", 1000]}, 3600] }] }, 3600 ]
},
"Male" : {
    "$sum": {
        "$cond": [ { "$eq": [ "$Gen", 0 ] }, 1, 0 ]
    }
    },
    "Female" : {
    "$sum": {
        "$cond": [ { "$eq": [ "$Gen", 1 ] }, 1, 0 ]
    }
    }
} }

Upvotes: 1

chridam
chridam

Reputation: 103425

You would need a mechanism to get the actual date object from the unix timestamp, one way is to add the timestamp to a zero-milliseconds Date() object, using the $add operator in the $project stage before the actual grouping aggregation pipeline.

Once you get the date, extract the hour part by using the $hour operator, something like the following:

db.FaceData.aggregate([ 
    {
        "$match": { 
            "Timestamp" : { $gte : 1448121600000, $lt : 1448294399000 }, 
            "DID" : "ABFR001" 
        }
    },
    {
        $project : {
            "hourPart" : {
                "$hour": { "$add": [ new Date(0), "$Timestamp" ] }
            },
            "Gen": 1
        }
    },  
    {
        "$group": { 
            "_id": "$hourPart",  
            "Gen_0_count" : {
                "$sum": {
                    "$cond": [ { "$eq": [ "$Gen", 0 ] }, 1, 0 ]
                }
            },
            "Gen_1_count" : {
                "$sum": {
                    "$cond": [ { "$eq": [ "$Gen", 1 ] }, 1, 0 ]
                }
            }
        }
    }  
]);

Upvotes: 1

Related Questions