title: Mongodb group collection entries by day from timestamp, performance optimisation
body: <p>I have a Node web app that uses mongodb to store data. I have a Messages collection that looks something like:</p>
  
  <pre><code>[
    {
      "message": "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut",
      "pubDate": "2014-01-16T14:15:33.792Z",
      "pubTimezone": "America/Tijuana"
      "id": "a9baa2f0-7eb8-11e3-b732-0b0c79e81098"
    },
    {
      "message": "labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco",
      "pubDate": "2014-01-16T14:01:24.198Z",
      "pubTimezone": "America/Tijuana"
      "id": "af550860-7eb6-11e3-b732-0b0c79e81098"
    },
    {
      "message": "laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in",
      "pubDate": "2014-01-17T01:12:27.277Z",
      "pubTimezone": "America/Tijuana"
      "id": "6dff53d0-7f14-11e3-b732-0b0c79e81098"
    },
    {
      "message": "voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat",
      "pubDate": "2014-01-17T01:10:17.249Z",
      "pubTimezone": "America/Tijuana"
      "id": "207ea110-7f14-11e3-b732-0b0c79e81098"
    }
  ...
  ]
  </code></pre>
  
  <p>I'd like to run a query that groups by day based on the pubDate timestamp, resulting in output:</p>
  
  <pre><code>[
    {
      "items":[
        {
          "message":"Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut",
          "pubDate":"2014-01-16T14:15:33.792Z",
          "pubTimezone": "America/Tijuana"
          "id":"a9baa2f0-7eb8-11e3-b732-0b0c79e81098"
        },
        {
          "message":"labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco",
          "pubDate":"2014-01-16T14:01:24.198Z",
          "pubTimezone": "America/Tijuana"
          "id":"af550860-7eb6-11e3-b732-0b0c79e81098"
        }
      ],
      "day":"2014-01-16T00:00:00.000Z"
    },
    {
      "items":[
        {
          "message":"laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in",
          "pubDate":"2014-01-17T01:12:27.277Z",
          "pubTimezone": "America/Tijuana"
          "id":"6dff53d0-7f14-11e3-b732-0b0c79e81098"
        },
        {
          "message":"voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat",
          "pubDate":"2014-01-17T01:10:17.249Z",
          "pubTimezone": "America/Tijuana"
          "id":"207ea110-7f14-11e3-b732-0b0c79e81098"
        }
      ],
      "day":"2014-01-17T00:00:00.000Z"
    }
  ...
  ]
  </code></pre>
  
  <p>Ordered reverse day date order, the timezone has to be used to calculate the day date.
  Results paged 50 per page.<br>
  Note that some days there are no items created, and the pubDates are all ISODate format in the db.</p>
  
  <p>I've read through a few posts about grouping however they all seem to do summation/count tasks rather than having all results grouped by day, in the output format I'm looking for. I've also read some blog <a href="http://www.kamsky.org/1/post/2013/03/stupid-date-tricks-with-aggregation-framework.html" rel="nofollow">posts</a> on aggregation frameworks and map reduce but not making much progress.</p>
  
  <p>It would be great if it was possible to get total_messages and total_days as part of the same query, though I can always do that in code when I get the results.</p>
  
  <p>The way I have my web app working, it pulls all messages in date desc order then in code sorts them by day, and the performance is pretty terrible, so I'm hoping to find a way to do the number crunching as the data is coming out of the db.</p>
  
  <p>Any guidance and/or help greatly appreciated.</p>