Skip to content
Advertisement

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:

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:

The output from this statement is as follow:

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.

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:

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:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement