Statement of total precipitation for each hour, day or month

Question

I store the data from the rain gauge in mySQL and needed help with listing the data. I save the data every 5 minutes and use the save date in the form of a timestamp. At the end of each hour, therefore, HH: 55 will always be the maximum hourly total that I need to list for the day DD-MM-YYYY

Is it possible to list in the second SELECT a daily or monthly total of the data stored in this way? If so, how? Thank you very much.

EDIT: Mysql version: 5.7.32-35-log I save the data every 5 minutes, but at the end of HH: 55 there is always the highest value for the hourly total precipitation.

So I have:

id | rain | timestamp
1 | 0.0 | 2021-10-04 12:00:00
2 | 0.0 | 2021-10-04 12:05:00
3 | 0.6 | 2021-10-04 12:10:00
4 | 0.9 | 2021-10-04 12:15:00
5 | 0.9 | 2021-10-04 12:20:00
6 | 1.4 | 2021-10-04 12:25:00
........
12 | 2.5 | 2021-10-04 12:55:00 // MAX rain

13 | 0.0 | 2021-10-04 13:00:00 // new hour and clean rain to 0.0
14 | 0.0 | 2021-10-04 13:05:00
15 | 0.6 | 2021-10-04 13:10:00

Answer 1

I would suggest that you instead of pick one point in time every hour, choose the maximum amount of rain per hour

SELECT date(ts), hour(ts), max(rain)
FROM t
GROUP BY date(ts), hour(ts)

Answer 2

I think that your approach is flawed. If you're measuring precipitation, then you want to measure from (a nano-second after) the beginning of the hour to the end of that hour (i.e. from 13:00:00.000000001 to 14:00:00.000000000) and not from a ns after 13:55 to 14:05.

Your approach essentially misses out on the 5 minutes of precipitation between 55 minutes past the hour and the hour mark!

This can be best shown by an example (the code below is available on the fiddle here):

CREATE TABLE prec -- precipitation and NOT rain! My dad was a meterologist
(
  id INT NOT NULL,
  rain FLOAT NOT NULL,
  ts   TIMESTAMP NOT NULL
);

populate it:

Now, I think that you're approaching this in the wrong way!

What you want is something more like the precipitation between, say, 13:00:00 and 13:05:00, and then between 13:05:00 and 13:10:00.

So, we assume that precipitation (just after) 13:00:00 is zero, but that a measurement at 13:00:00 represents it between 12:00:00 and 13:00:00, so I would have something more like this:

Basically, the first measurement for any period is at 5 minutes past the hour and not on the hour!

INSERT INTO prec VALUES

(0, 3.5, '2021-10-04 11:55:00'),
(1, 4.2, '2021-10-04 12:00:00'),  -- last measurement - highest

--
-- starts again after the hour
--


(2 ,  0.2, '2021-10-04 12:05:00'),  -- first measurement for period
(3 ,  0.6, '2021-10-04 12:10:00'),
(4 ,  0.9, '2021-10-04 12:15:00'),
(5 ,  0.9, '2021-10-04 12:20:00'),
(6 ,  1.4, '2021-10-04 12:25:00'),
(12,  2.5, '2021-10-04 12:55:00'),

(13, 2.6, '2021-10-04 13:00:00'),    -- last measurement for period


--
-- starts again **FROM** the hour!
--

(14,  0.1, '2021-10-04 13:05:00'),  -- 05:00 - lowest
(15,  0.6, '2021-10-04 13:10:00'),
(24,  1.5, '2021-10-04 13:55:00'),
(25,  1.8, '2021-10-04 14:00:00');  -- 00:00 - highest

Queries:

Query 1 (flawed)

SELECT 
  rain, ts, DATE(ts), HOUR(ts), MINUTE(ts)
FROM
  prec
WHERE MINUTE(ts) = 55;

Result:

rain    ts  DATE(ts)    HOUR(ts)    MINUTE(ts)
3.5     2021-10-04 11:55:00     2021-10-04  11  55
2.5     2021-10-04 12:55:00     2021-10-04  12  55
1.5     2021-10-04 13:55:00     2021-10-04  13  55

Query 2 (correct way)

SELECT
  rain, ts, DATE(ts), HOUR(ts), MINUTE(ts)
FROM
  prec 
WHERE MINUTE(ts) = 0;

Result:

rain    ts  DATE(ts)    HOUR(ts)    MINUTE(ts)
4.2     2021-10-04 12:00:00     2021-10-04  12  0
2.6     2021-10-04 13:00:00     2021-10-04  13  0
1.8     2021-10-04 14:00:00     2021-10-04  14  0

So, the MINUTE(ts) = 55 is missing out data!

You might be better off taking a cumulative approach to precipation over, say, a year and doing subtractions. With window functions (LAG()/LEAD()), this should be easy enough!

Answer 3

Do not mix hourly, daily, weekly, etc in a single table. I might have 3 tables:

• A staging table for the 12 rows for the current hour (starting at :00, ..., :55). It is TRUNCATEd each hour as the data is summarized into the next table. (If you want the 5-minute details for longer, then don't promptly it.)
• A table with hourly data. If this table will be eventually purged, I would PARTITION BY RANGE(TO_DAYS(..))
• A daily summary table, with totals for each day.
• No weekly (etc) summary table; such is easily enough derived from the daily table.

If this is only for rainfall, consider not storing hours or days with a value of "0". This can make some queries more complex, but it will save a huge amount of space if you are in the Sahara.