title: Count unique number of days between 2 dates where each day will have multiple rows in MySQL
body: <p>I have a mysql database with about 4.5 million rows of weather data since 2004 in it. I am trying to find out how many days in a time period that the temperature was at or below 0. Below is an example of the data (many more columns than just temperature) Data is collected every minute. I realize that my date and time columns should be in a single datetime column and I do use that now but it only has data for the last few months in it.</p>
  
  <pre><code>date        time        temperature
  2014-02-01  23:58:00    -20.3
  2014-02-01  23:59:00    -20.4
  ---         ---         ---
  2014-02-02  00:00:00    -20.5
  2014-02-02  00:01:00    -20.5
  2014-02-02  00:02:00    -20.6
  ---         ---         ---
  2014-02-17  08:30:00    17.2
  2014-02-17  08:31:00    17.2
  </code></pre>
  
  <p>I can show the days for a month individually:</p>
  
  <p>mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2013' and month(date) = '12' and temperature &lt;= '0' group by day(date) order by day(date) asc;</p>
  
  <pre><code>+------------+----------+-------+
  | Ddate      | Ttime    | Temp  |
  +------------+----------+-------+
  | 2013-12-05 | 23:59:00 |  -3.6 |
  | 2013-12-06 | 23:59:00 | -22.7 |
  | 2013-12-07 | 23:59:00 | -25.2 |
  | 2013-12-08 | 23:59:00 |    -4 |
  ---------------------------------
  +------------+----------+-------+
  25 rows in set (6.95 sec)
  </code></pre>
  
  <p>The following does not work though because it only shows the data for January and nothing for February (through today which is February 17).</p>
  
  <p>mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2014' and temperature &lt;= '0' group by day(date) order by day(date) asc;</p>
  
  <pre><code>+------------+----------+-------+
  | Ddate      | Ttime    | Temp  |
  +------------+----------+-------+
  | 2014-01-01 | 00:00:00 | -20.7 |
  | 2014-01-02 | 00:00:00 | -28.8 |
  | 2014-01-03 | 00:00:00 | -12.5 |
  | 2014-01-04 | 08:39:00 |     0 |
  | 2014-01-05 | 00:00:00 | -19.8 |
  ---------------------------------
  | 2014-01-31 | 00:00:00 | -21.5 |
  +------------+----------+-------+
  28 rows in set (6.86 sec)
  </code></pre>
  
  <p>In order to get February, I need to do another select using the current month. So, while I can add the total rows manually (53 for December and January), I would still have to do another select for February and add in those days for a total of 68 days. I just want to get a count of the days in the timespan, not the total number of rows.</p>
  
  <p>Something along the lines of:</p>
  
  <p>select count_number_of_days from wx_data where temperature &lt;= 0;</p>
  
  <p>I hope this makes sense.</p>