1

In this table the values of my rain counter are stored. The correct total is 0.9 In the RAW Data you can see that at 17:01 the value was set to 1161.5.

By sql query the RAW data are grouped per hour. In the results only 0.6 rain are shown in the time from 16-17 o'clock. Unfortunately the 0.3 mm between counter 1161.5 and 1161.2 are missing.

Any idea how I can avoid the error in my sql query?

  • RAW Data from raincounter:
id unixt date rainc
15261 1666270927 2022-10-20 15:02:07 1160.6
15262 1666271527 2022-10-20 15:12:07 1160.6
15263 1666272127 2022-10-20 15:22:07 1160.6
15264 1666272727 2022-10-20 15:32:07 1160.6
15265 1666273327 2022-10-20 15:42:07 1160.6
15266 1666273927 2022-10-20 15:52:07 1160.6
15267 1666274527 2022-10-20 16:02:07 1160.6
15268 1666275127 2022-10-20 16:12:07 1160.6
15269 1666275727 2022-10-20 16:22:07 1160.6
15270 1666276171 2022-10-20 16:29:31 1160.9
15271 1666276228 2022-10-20 16:30:28 1160.9
15272 1666276327 2022-10-20 16:32:07 1160.9
15273 1666276927 2022-10-20 16:42:07 1160.9
15274 1666277348 2022-10-20 16:49:08 1161.2
15275 1666277527 2022-10-20 16:52:07 1161.2
15276 1666278118 2022-10-20 17:01:58 1161.5
15277 1666278127 2022-10-20 17:02:07 1161.5
15278 1666278727 2022-10-20 17:12:07 1161.5
15279 1666279327 2022-10-20 17:22:07 1161.5
15280 1666279927 2022-10-20 17:32:07 1161.5
  • MYSQL Query:
SELECT `date`,ROUND(MAX(`rainc`) - MIN(`rainc`),2) AS `rain`, MAX(`rainc`) AS max
FROM`mqtt-weather`.`rainc`
WHERE `date` >= CURDATE() AND `date` < CURDATE() + INTERVAL 1 DAY
GROUP BY HOUR(`date`)
  • Result:
date rain max
2022-10-20 15:02:07 0.00 1160.6
2022-10-20 16:02:07 0.60 1161.2
2022-10-20 17:01:58 0.00 1161.5
2022-10-20 18:02:07 0.00 1161.5
MatBailie
  • 83,401
  • 18
  • 103
  • 137
Quito96
  • 71
  • 5
  • Your sample data does not match your result set, please correct. – griv Oct 20 '22 at 19:07
  • 1
    i hope my correction looks better – Quito96 Oct 21 '22 at 05:45
  • You need to look at the last row in the previous group (the previous hour) and compare it to the last row in the current group (the current hour), not the min and max values in the same hour. That can be achieved with LAST_VALUE and/or LAG, which are available in MySQL 8. Please specify which version of MySQL you're using. (Also, grouping with the `HOUR` function alone will cause problems your data spans different dates.) – MatBailie Oct 21 '22 at 11:09
  • Version 10.3.36-MariaDB-0+deb10u1 (Raspbian 10) – Quito96 Oct 21 '22 at 12:01

1 Answers1

0
WITH
  lagged AS
(
  SELECT
    r.*,
    LAG(`date`) OVER (ORDER BY unixt) AS prev_date,
    rainc - LAG(rainc) OVER (ORDER BY unixt) AS rainc_delta
  FROM
    `mqtt-weather`.`rainc` AS r
)
SELECT
  MIN(prev_date)     AS interval_start,
  MAX(`date`)        AS interval_end,
  SUM(rainc_delta)   AS interval_rain,
  MAX(rainc)         AS final_rainc
FROM
  lagged
GROUP BY
  unixt DIV 3600

EDIT: added demo and corrected typos and integer division.

Demo: https://dbfiddle.uk/fW1ykZxi

MatBailie
  • 83,401
  • 18
  • 103
  • 137