0

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
3
  • 1
    HI and welcome to the community. Please have a look at: Minimal, Complete, and Verifiable Example
    – McNets
    Oct 4 at 9:04
  • Hi, and welcome to dba.se. Do you mean that you will only ever record the value at xxh:55min? I.e. for 11:00 hours, you will only keep the reading for 11:55? You're question isn't clear. Please go to dbfiddle.uk and provide a fiddle with your table structure(s) and some sample data and your desired result!
    – Vérace
    Oct 4 at 9:09
  • What is your version of MySQL?
    – Vérace
    Oct 4 at 9:19
0

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)
6
  • MAX(rain) will always be for 55 mins past the hour. However, this is incorrect (should be at hour:59:59.999999999 and very counterintuitive) - see my answer - any comments welcome!
    – Vérace
    Oct 4 at 13:06
  • Given the sample data by the poster I get the impression that the sensor is read every 5 minutes and reset every hour?
    – Lennart
    Oct 4 at 13:29
  • Any how, max(rain) per hour should give the maximum amount, regardless of how often the sensor is read
    – Lennart
    Oct 4 at 13:31
  • Given the sample data by the poster I get the impression that the sensor is read every 5 minutes and reset every hour? - which is wrong - it misses the data from the final 5 minutes of the hour!
    – Vérace
    Oct 4 at 13:35
  • 1
    MAX(rain) should be on the hour (for the previous hour) and not 5 minutes to the hour - but you are correct - given the OP's current scenario, your answer will work - it will give the the value at HOUR:55 - which I think is not the way it should be done! :-)
    – Vérace
    Oct 4 at 13:37
0

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!

3
  • .Well, it is not "missing out data" if :55 holds the 5-minute rainfall starting at :55.
    – Rick James
    Oct 4 at 17:05
  • Agree on recording annual rainfall instead of rain-since-local-midnight. Avoids the problem with accumulation lost between the last observation and midnight. You only have to deal with rollover once, at the beginning of your rain year. I recently converted my own site to use accumulated rainfall and the queries run much faster and things like 'rain last 24 hours' are trivial to calculate. This of course assumes your weather station datalogger records accumulated rainfall.
    – dwhitemv
    Oct 5 at 3:53
  • @dwhitemv - this can actually have legal ramifications! One time, my dad told me about a guy who was obviously blind drunk and fell out of his own hotel room window... (yeah, I know...) and was trying to bring a case against the hotel - the court required an expert witness from Met Éireann to tell the court about weather conditions at the day and time in question!
    – Vérace
    Oct 5 at 7:47
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.