CODEWITHSUNDEEP
mysql
Bill
Bill

Reputation: 15

Count unique number of days between 2 dates where each day will have multiple rows in MySQL

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.

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

I can show the days for a month individually:

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 <= '0' group by day(date) order by day(date) asc;

+------------+----------+-------+
| 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)

The following does not work though because it only shows the data for January and nothing for February (through today which is February 17).

mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2014' and temperature <= '0' group by day(date) order by day(date) asc;

+------------+----------+-------+
| 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)

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.

Something along the lines of:

select count_number_of_days from wx_data where temperature <= 0;

I hope this makes sense.

Upvotes: 1

Views: 566

Answers (2)

Linger
Linger

Reputation: 15058

If you are just looking for a count of the days below zero for a specified time frame then you could use the following (SQL Fiddle):

SELECT COUNT(*)
FROM
(
  SELECT DISTINCT  m.date
  FROM MyTable AS m
  WHERE m.temperature < 0
  AND Year(m.date) = 2014
) AS mm

In the example SQL Fiddle there are unique days with multiple temperature readings during the day. If any of those readings fall below zero then it is counted. As you can see there are four unique days of which only three of those fall below zero.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You don't want to use the day() function. That returns the day of the month. You want date():

select date(date) as Ddate, time as Ttime, temperature as Temp
from wx_data
where Year(date) = 2014 and temperature <= 0
group by date(date)
order by date(date)

I assume your temperature is stored as a number, so I removed the single quotes. The function year() returns a number, so "2014" should not have single quotes either.

Actually, your dates are just dates with no time, so you can do:

select date as Ddate, time as Ttime, temperature as Temp
from wx_data
where Year(date) = 2014 and temperature <= 0
group by date
order by date

Note that this will return values of time and temperature from arbitrary matching rows. It is unclear what you really want for these columns.

If you want the number of such days remove the final group by and turn this into an aggregation query:

select count(distinct date)
from wx_data
where Year(date) = 2014 and temperature <= 0;

If you want the number between two dates, then use something like:

where date >= '2014-01-01' and date < '2015-01-01'

Upvotes: 1

Related Questions