How to group by seconds without the ISODate decimal part in MongoDB

1.6k views Asked by At

I wanted to query the database in order to find the number of post per second to feed into a graph to show activity trend. I use spring-data-mongo but for now, the first step is to do this in the mongo shell before worrying about how to do from java.

I used the aggregation framework on it as shown below:

 db.post.group({
key:{dateCreated: 1},
cond: { dateCreated:
        {
          "$gt": new ISODate("2013-08-09T05:51:15Z"),
          "$lt": new ISODate("2013-08-09T05:51:20Z")
        }
      },
reduce: function(cur, result){
   result.count += 1
},
 initial: {count:0}
})

The result is encouraging but is seems because of the decimal part of the ISODate, the count seems wrong as it does group per seconds with the decimal making each count 1.

 [
{
    "dateCreated" : ISODate("2013-08-09T05:51:15.332Z"),
    "count" : 1
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:15.378Z"),
    "count" : 1
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:15.377Z"),
    "count" : 1
},
 // many more here
 ]

Is there a way to just consider only the seconds part as in result like below:

 [
{
    "dateCreated" : ISODate("2013-08-09T05:51:15Z"),
    "count" : 5
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:16Z"),
    "count" : 8
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:17Z"),
    "count" : 3
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:18Z"),
    "count" : 10
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:19Z"),
    "count" : 2
},
{
    "dateCreated" : ISODate("2013-08-09T05:51:20Z"),
    "count" : 13
} 

 ]

Thank for reading this.

1

There are 1 answers

0
black sensei On BEST ANSWER

For those in the same situation. here is how I modified my query. Thanks to @Sammaye.

db.post.aggregate(
{
  $match: { dateCreated:
                    {
                      "$gt": new ISODate("2013-08-09T05:51:15.000Z"),
                      "$lt": new ISODate("2013-08-09T05:51:20.000Z")
                    }
           }
},
{ 
  $group: {
        _id: {
            hour: {$hour: "$dateCreated"},
            minute: {$minute: "$dateCreated"},
            second: {$second: "$dateCreated"}   
        },
        cnt: {$sum : 1}
       }
}
)
   {
"result" : [
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 19
        },
        "cnt" : 26
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 18
        },
        "cnt" : 29
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 17
        },
        "cnt" : 27
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 16
        },
        "cnt" : 25
    },
    {
        "_id" : {
            "hour" : 5,
            "minute" : 51,
            "second" : 15
        },
        "cnt" : 16
    }
],
"ok" : 1
   }