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.
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!
Advertisement
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