Find the longest rainless time (every hour, beyond several days)

I have a weather station where I measure the amount of rain per hour and save the data in a database. The rain-table looks like this:

CREATE TABLE `Rain` (
  `id` int(11) NOT NULL,
  `rain` double NOT NULL,
  `date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Rain` (`id`, `rain`, `date_time`) VALUES
(1, 0, '2020-07-12 21:00:01'),
(2, 0, '2020-07-12 22:00:01'),
(3, 0, '2020-07-12 23:00:01'),
(4, 0, '2020-07-13 00:00:01'),
(5, 0, '2020-07-13 01:00:01'),
(6, 0, '2020-07-13 02:00:01'),
(7, 0, '2020-07-13 03:00:01');

Now I want the longest rainless time per month. I already have a solution, but it only works for the respective day. However, if there is no rain for several days, my solution will not work correctly…

My current statement looks like this:

SET @row_number:=0;
SELECT rain, date_time, 
        EXTRACT(DAY_HOUR FROM date_time) - @row_number:=@row_number+1 as 'check_sum'
FROM Rain 
WHERE rain=(SELECT MIN(rain) 
            FROM Rain 
            WHERE date_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'
            ) 
AND date_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'

The output from this statement is as follow:

rain date_time           check_sum
   0 2020-07-12 21:00:01      1220
   0 2020-07-12 22:00:02      1220
   0 2020-07-12 23:00:01      1220 
   0 2020-07-13 00:00:01      1296
   0 2020-07-13 01:00:01      1296
   0 2020-07-13 02:00:01      1296
   0 2020-07-13 03:00:01      1296
   0 2020-07-13 04:00:01      1296
   0 2020-07-13 04:00:01      1296

Although it didn’t rain, I get a different checksum for the following day.

SQLFIDDLE

I also know why, but I don’t know how I can solve it differently in order to achieve what I intend to do.

day/hour       @row_number+1
(12/21) 1221 -       1       = 1220 same
(12/22) 1222 -       2       = 1220 same
(12/23) 1223 -       3       = 1220 same
(13/00) 1300 -       4       = 1296 not same (day changed)
(13/01) 1301 -       5       = 1296 not same (day changed)
(13/02) 1302 -       6       = 1296 not same (day changed)

Does anyone know how to do it despite the change of day and the same check sum? Or is there another way (completely new approach/new query) to solve my problem?

Thanks in advance!

Answer

In older versions, you can use variables:

SELECT MIN(date_time), MAX(date_time),
       TIMESTAMPDIFF(hour, MIN(date_time), MAX(date_time)) hours
FROM (SELECT r.*, (@rn := @rn + 1) as rn
      FROM (SELECT r.*, FLOOR(UNIX_TIMESTAMP(date_time) / 3600) as hh
            FROM Rain r
            WHERE rain = 0
            ORDER BY date_time
           ) r CROSS JOIN
           (SELECT @rn := 0) params
      ) r
GROUP BY (hh - rn)
ORDER BY hours desc

Add LIMIT 1 to get the top number of hours.

The idea is to calculate the number of hours (since the beginning of time) for each rainless day. If you subtract a sequential number from the number of hours, you get a constant for hours that are in sequence.

Note that this approach also works with window functions as well:

SELECT MIN(date_time), MAX(date_time),
       TIMESTAMPDIFF(hour, MIN(date_time), MAX(date_time)) hours
FROM (SELECT r.*, FLOOR(UNIX_TIMESTAMP(date_time) / 3600) as hh,
             ROW_NUMBER() OVER (ORDER BY date_time) as rn
      FROM Rain r
      WHERE rain = 0
     ) r
GROUP BY (hh - rn)
ORDER BY hours desc