title: MySQL: Retrieving count of records from a particular time frame
body: <p>So what I'm trying to do is a bit tricky.  I'm trying to come up with a sql query that will be able to return counts for specified time frames.  So I have a table 'v'.  Here is some basic data from Table v</p>
  
  <pre><code>Table v
  _____________
  id    p_id    created_at
   1     1      2009-06-09 18:54:17
   2     2      2009-06-09 21:51:24
   3     2      2009-06-10 18:53:51
   4     1      2009-06-10 01:20:36
   5     1      2009-06-10 11:20:36
  </code></pre>
  
  <p>Basically, I want to get results back for a specified time frame (hour, day, week, month, year).  I've got this somewhat working for days...but am unable to return results for timeframs containing a count of zero.  Basically I want to give it a time frame and a delimiter(hour, day, etc.) and be able to get the number of rows from table v within that time frame.</p>
  
  <p>This is what I currently tried:</p>
  
  <pre><code>select count(*) as count, date_format(created_at, "%m/%d/%y") as date from v where p_id = 56 group by date_format(created_at, "%m/%d/%y");
  </code></pre>
  
  <p>Returns</p>
  
  <pre><code>+-------+-------------------------------------+
  | count | date_format(created_at, "%m/%d/%y") |
  +-------+-------------------------------------+
  |     3 | 06/09/09                            | 
  +-------+-------------------------------------+
  </code></pre>
  
  <p>But this doesn't take into consideration a time frame.  Any ideas?</p>