MongoDB group by UNIX time and count not working

Question

I have a document which looks like,

{
    "_id" : ObjectId("57dffd3b65291f06dab34385"),
    "user" : "12345667890"
    "time" : NumberLong(1474297140186)
}

I am trying to count number of visitors for a certain application. I have 64 fields and my query looks as below.

db.convo_log.aggregate([
   {
       '$group': {
            '_id': { 
                month: { $month: new Date("$time") }, 
                day: { $dayOfMonth: new Date("$time") }, 
                year: { $year: new Date("$time") } 
            }, 
            'count': { '$sum': 1 }
       }
   }
])

Even though I am looking for a several groups (as there is data from different days) it returns as,

{
    "_id" : {
        "month" : 8,
        "day" : 17,
        "year" : 292278994
    },
    "count" : 64.0
}

Is there anything I am doing wrong here?

Answer 1

You can't create a date object representation from a timestamp that way. You would need to do some arithmetic manipulation using the arithmetic operators i.e. $add the timestamp to a new Date(0) object constructor which represents the date as 0 millisecond from epoch (though in a shorter form).

The sum { "$add": ["$time", new Date(0)] } results in a new date object. So putting this altogether with the $dateToString operator, you can run this pipeline to get the desired result:

db.convo_log.aggregate([
    {
        "$project": {
            "formattedDate": {
                "$dateToString": {
                    "format": "%Y-%m-%d",
                    "date": { "$add": ["$time", new Date(0)] }
                }
            }
        }
    },
    {
        "$group": {
            "_id": "$formattedDate",
            "count": { "$sum": 1 }
        }
    }
])