title: Counting daily and hourly results in mySQL
body: <p>I have two tables in mySQL, one for views and one for downloads with the following key structure:</p>
  
  <pre><code>ip          | hit
  ---------------------------------------
  127.0.0.1   | 2015-02-08 15:16:59
  127.0.0.1   | 2015-02-08 15:20:22   
  127.0.0.1   | 2015-02-08 15:20:35   
  127.0.0.1   | 2015-02-08 15:21:13
  10.0.1.1    | 2015-02-09 16:29:13
  10.0.1.1    | 2015-02-09 16:42:12
  10.0.1.1    | 2015-02-09 16:52:30
  10.0.1.1    | 2015-02-10 10:52:30
  10.0.1.1    | 2015-02-10 10:52:30
  10.0.1.1    | 2015-02-10 12:52:30
  </code></pre>
  
  <p>I need to return a query that will count how many rows there are every day, ideally it would also return empty days/hours but I can fill those gaps in with PHP easily if the SQL query would be overly complex.</p>
  
  <pre><code>time        | count
  ---------------------------------------
  2015-02-08  | 4
  2015-02-09  | 3
  2015-02-10  | 3
  </code></pre>
  
  <p>I also need to get the same information on a hourly basis</p>
  
  <pre><code>time                          | count
  ---------------------------------------
  2015-02-08 15:00:00           | 4
  2015-02-09 16:00:00           | 3
  2015-02-10 10:00:00           | 2
  2015-02-10 10:00:00           | 1
  </code></pre>
  
  <p>So far i have the following query after cobbling together a few I found online into something that actually works, but it only returns the day of the month as being unique and I need to have the query be stretchable over multiple months so only knowing the day is not massively helpful.</p>
  
  <pre><code>select dayofmonth(hit) as Day_of_month, count(*) as records from table_name hit &gt;= '2015-02-01 00:00:00' and hit &lt;  '2015-03-01 00:00:00' group by dayofmonth(hit)
  </code></pre>
  
  <p>I've seen a fair few of these similar posts around but the only ones I have found use functions not present in mySQL it seems, any help would be appreciated.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>After much more fiddling I now have it returning counts for each date with the following which i can easily enough limit by wheres, however I'm still none the wiser to getting this to work on an hourly basis.</p>
  
  <pre><code>SELECT date(hit) AS The_Date, count( * ) AS count FROM table_name GROUP BY The_Date ORDER BY The_Date ASC
  </code></pre>