group by dates in mongodb

I am working on a project in which I am tracking number of clicks on a topic.

I am using mongodb and I have to group number of click by date( i want to group data for 15 days).

I am having data store in following format in mongodb

{ 
   "_id" : ObjectId("4d663451d1e7242c4b68e000"), 
  "date" : "Mon Dec 27 2010 18:51:22 GMT+0000 (UTC)", 
  "topic" : "abc", 
  "time" : "18:51:22"
}
{ 
    "_id" : ObjectId("4d6634514cb5cb2c4b69e000"), 
    "date" : "Mon Dec 27 2010 18:51:23 GMT+0000 (UTC)", 
    "topic" : "bce", 
    "time" : "18:51:23"
}

i want to group number of clicks on topic:abc by days(for 15 days)..i know how to group that but how can I group by date which are stored in my database

I am looking for result in following format

[
  {
    "date" : "date in log",
    "click" : 9 
  },  
  {
    "date" : "date in log",
    "click" : 19
  },  
]

I have written code but it will work only if date are in string (code is here http://pastebin.com/2wm1n1ix) ...please guide me how do I group it

New answer using Mongo aggregation framework

After this question was asked and answered, 10gen released Mongodb version 2.2 with an aggregation framework, which is now the better way to do this sort of query. This query is a little challenging because you want to group by date and the values stored are timestamps, so you have to do something to convert the timestamps to dates that match. For the purposes of example I will just write a query that gets the right counts.

db.col.aggregate(
   { $group: { _id: { $dayOfYear: "$date"},
               click: { $sum: 1 } } }
   )

This will return something like:

[
    {
        "_id" : 144,
        "click" : 165
    },
    {
        "_id" : 275,
        "click" : 12
    }
]

You need to use $match to limit the query to the date range you are interested in and $project to rename _id to date. How you convert the day of year back to a date is left as an exercise for the reader. :-)

10gen has a handy SQL to Mongo Aggregation conversion chart worth bookmarking. There is also a specific article on date aggregation operators.

Getting a little fancier, you can use:

db.col.aggregate([
  { $group: {
      _id: {
        $add: [
         { $dayOfYear: "$date"}, 
         { $multiply: 
           [400, {$year: "$date"}]
         }
      ]},   
      click: { $sum: 1 },
      first: {$min: "$date"}
    }
  },
  { $sort: {_id: -1} },
  { $limit: 15 },
  { $project: { date: "$first", click: 1, _id: 0} }
])

which will get you the latest 15 days and return some datetime within each day in the date field. For example:

[
    {
        "click" : 431,
        "date" : ISODate("2013-05-11T02:33:45.526Z")
    },
    {
        "click" : 702,
        "date" : ISODate("2013-05-08T02:11:00.503Z")
    },
            ...
    {
        "click" : 814,
        "date" : ISODate("2013-04-25T00:41:45.046Z")
    }
]

There are already many answers to this question, but I wasn't happy with any of them. MongoDB has improved over the years, and there are now easier ways to do it. The answer by Jonas Tomanga gets it right, but is a bit too complex.

If you are using MongoDB 3.0 or later, here's how you can group by date. I start with the $match aggregation because the author also asked how to limit the results.

db.yourCollection.aggregate([
  { $match: { date: { $gte: ISODate("2019-05-01") } } },
  { $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$date"} }, count: { $sum: 1 } } },
  { $sort: { _id: 1} }
])

Late answer, but for the record (for anyone else that comes to this page): You'll need to use the 'keyf' argument instead of 'key', since your key is actually going to be a function of the date on the event (i.e. the "day" extracted from the date) and not the date itself. This should do what you're looking for:

db.coll.group(
{
    keyf: function(doc) {
        var date = new Date(doc.date);
        var dateKey = (date.getMonth()+1)+"/"+date.getDate()+"/"+date.getFullYear()+'';
        return {'day':dateKey};
    },
    cond: {topic:"abc"},
    initial: {count:0},
    reduce: function(obj, prev) {prev.count++;}
});

For more information, take a look at MongoDB's doc page on aggregation and group: http://www.mongodb.org/display/DOCS/Aggregation#Aggregation-Group