I am receiving rainfall data from my weather station in one minutes intervals e.g:
+---------------------+----------+
| tid | regn1min |
+---------------------+----------+
| 2024-09-09 05:00:11 | 0.00 |
| 2024-09-09 05:01:10 | 0.00 |
| 2024-09-09 05:02:10 | 0.00 |
| 2024-09-09 05:03:10 | 0.00 |
| 2024-09-09 05:04:10 | 0.00 |
| 2024-09-09 05:05:11 | 0.00 |
| 2024-09-09 05:06:10 | 0.00 |
| 2024-09-09 05:07:10 | 0.00 |
| 2024-09-09 05:08:10 | 0.41 |
| 2024-09-09 05:09:10 | 0.00 |
| 2024-09-09 05:10:11 | 0.00 |
| 2024-09-09 05:11:10 | 0.00 |
| 2024-09-09 05:12:10 | 0.00 |
| 2024-09-09 05:13:10 | 0.00 |
| 2024-09-09 05:14:10 | 0.41 |
| 2024-09-09 05:15:10 | 0.00 |
| 2024-09-09 05:16:11 | 0.00 |
| 2024-09-09 05:17:10 | 0.00 |
| 2024-09-09 05:18:10 | 0.00 |
| 2024-09-09 05:19:10 | 0.00 |
| 2024-09-09 05:20:10 | 0.00 |
| 2024-09-09 05:21:10 | 0.41 |
| 2024-09-09 05:22:11 | 0.00 |
| 2024-09-09 05:23:10 | 0.00 |
| 2024-09-09 05:24:10 | 0.00 |
| 2024-09-09 05:25:10 | 0.41 |
| 2024-09-09 05:26:10 | 0.00 |
| 2024-09-09 05:27:10 | 0.00 |
| 2024-09-09 05:28:10 | 0.00 |
| 2024-09-09 05:29:10 | 0.41 |
| 2024-09-09 05:30:10 | 0.00 |
| 2024-09-09 05:31:10 | 0.00 |
| 2024-09-09 05:32:10 | 0.41 |
| 2024-09-09 05:33:10 | 0.00 |
| 2024-09-09 05:34:11 | 0.00 |
| 2024-09-09 05:35:10 | 0.41 |
| 2024-09-09 05:36:10 | 0.00 |
| 2024-09-09 05:37:10 | 0.41 |
| 2024-09-09 05:38:10 | 0.00 |
| 2024-09-09 05:39:11 | 0.00 |
| 2024-09-09 05:40:10 | 0.00 |
| 2024-09-09 05:41:10 | 0.00 |
| 2024-09-09 05:42:10 | 0.00 |
| 2024-09-09 05:43:10 | 0.41 |
| 2024-09-09 05:44:10 | 0.00 |
| 2024-09-09 05:45:11 | 0.00 |
| 2024-09-09 05:46:10 | 0.00 |
| 2024-09-09 05:47:10 | 0.41 |
| 2024-09-09 05:48:10 | 0.00 |
| 2024-09-09 05:49:10 | 0.00 |
| 2024-09-09 05:50:11 | 0.00 |
| 2024-09-09 05:51:10 | 0.00 |
| 2024-09-09 05:52:10 | 0.41 |
| 2024-09-09 05:53:10 | 0.00 |
| 2024-09-09 05:54:10 | 0.00 |
| 2024-09-09 05:55:10 | 0.41 |
| 2024-09-09 05:56:11 | 0.00 |
| 2024-09-09 05:57:10 | 0.00 |
| 2024-09-09 05:58:10 | 0.41 |
| 2024-09-09 05:59:10 | 0.00 |
+---------------------+----------+
What I like to have is a running sum that will show rainfall in 15 minutes, 1 hour and 15 minutes like:
+---------------------+----------+
| tid | regn1min | regn15min | regn1h | regn24h |
+---------------------+----------+
| 2024-09-09 05:00:11 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:01:10 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:02:10 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:03:10 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:04:10 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:05:11 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:06:10 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:07:10 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2024-09-09 05:08:10 | 0.41 | 0.41 | 0.41 | 0.41 |
| 2024-09-09 05:09:10 | 0.00 | 0.41 | 0.41 | 0.41 |
| 2024-09-09 05:10:11 | 0.00 | 0.41 | 0.41 | 0.41 |
| 2024-09-09 05:11:10 | 0.00 | 0.41 | 0.41 | 0.41 |
| 2024-09-09 05:12:10 | 0.00 | 0.41 | 0.41 | 0.41 |
| 2024-09-09 05:13:10 | 0.00 | 0.41 | 0.41 | 0.41 |
| 2024-09-09 05:14:10 | 0.41 | 0.82 | 0.82 | 0.82 |
| 2024-09-09 05:15:10 | 0.00 | 0.82 | 0.82 | 0.82 |
| 2024-09-09 05:16:11 | 0.00 | 0.82 | 0.82 | 0.82 |
| 2024-09-09 05:17:10 | 0.00 | 0.82 | 0.82 | etc |
| 2024-09-09 05:18:10 | 0.00 | 0.82 | 0.82 | vvvv |
| 2024-09-09 05:19:10 | 0.00 | 0.82 | 0.82 | 0.00 |
| 2024-09-09 05:20:10 | 0.00 | 0.82 | 0.82 | 0.00 |
| 2024-09-09 05:21:10 | 0.41 | 1.23 | 1.23 | 0.00 |
| 2024-09-09 05:22:11 | 0.00 | 1.23 | 1.23 | 0.00 |
| 2024-09-09 05:23:10 | 0.00 | 0.82 | 1.23 | 0.00 |
| 2024-09-09 05:24:10 | 0.00 | 0.82 | 1.23 | 0.00 |
| 2024-09-09 05:25:10 | 0.41 | 1.23 | 1.64 | 0.00 |
| 2024-09-09 05:26:10 | 0.00 | 1.23 | 1.64 | 0.00 |
| 2024-09-09 05:27:10 | 0.00 | 1.23 | 1.64 | 0.00 |
| 2024-09-09 05:28:10 | 0.00 | 0.82 | 1.64 | 0.00 |
| 2024-09-09 05:29:10 | 0.41 | 1.23 | 2.05 | 0.00 |
| 2024-09-09 05:30:10 | 0.00 | 1.23 | 2.05 | 0.00 |
| 2024-09-09 05:31:10 | 0.00 | 1.23 | 2.05 | 0.00 |
| 2024-09-09 05:32:10 | 0.41 | 1.64 | 2.46 | 0.00 |
| 2024-09-09 05:33:10 | 0.00 | 1.64 | 2.46 | 0.00 |
| 2024-09-09 05:34:11 | 0.00 | 1.64 | 2.26 | 0.00 |
| 2024-09-09 05:35:10 | 0.41 | 1.64 | 2.67 | 0.00 |
| 2024-09-09 05:36:10 | 0.00 | 1.64 | 2.67 | 0.00 |
| 2024-09-09 05:37:10 | 0.41 | 2.05 | 3.08 | 0.00 |
| 2024-09-09 05:38:10 | 0.00 | 1.64 | 3.08 | 0.00 |
| 2024-09-09 05:39:11 | 0.00 | 2.05 | 3.08 | 0.00 |
| 2024-09-09 05:40:10 | 0.00 | 2.05 | 3.08 | 0.00 |
| 2024-09-09 05:41:10 | 0.00 | 1.64 | 3.08 | 0.00 |
| 2024-09-09 05:42:10 | 0.00 | 1.64 | 3.08 | 0.00 |
| 2024-09-09 05:43:10 | 0.41 | 2.05 | 3.49 | 0.00 |
| 2024-09-09 05:44:10 | 0.00 | 1.64 | 3.49 | 0.00 |
| 2024-09-09 05:45:11 | 0.00 | 1.64 | 3.49 | 0.00 |
| 2024-09-09 05:46:10 | 0.00 | 1.23 | 3.49 | 0.00 |
| 2024-09-09 05:47:10 | 0.41 | 1.64 | 3.90 | 0.00 |
| 2024-09-09 05:48:10 | 0.00 | 1.23 | 3.90 | 0.00 |
| 2024-09-09 05:49:10 | 0.00 | 1.23 | 3.90 | 0.00 |
| 2024-09-09 05:50:11 | 0.00 | 1.23 | 3.90 | 0.00 |
| 2024-09-09 05:51:10 | 0.00 | 0.82 | 3.90 | 0.00 |
| 2024-09-09 05:52:10 | 0.41 | 1.23 | 4.31 | 0.00 |
| 2024-09-09 05:53:10 | 0.00 | 1.23 | 4.31 | 0.00 |
| 2024-09-09 05:54:10 | 0.00 | 1.23 | 4.31 | 0.00 |
| 2024-09-09 05:55:10 | 0.41 | 1.64 | 4.72 | 0.00 |
| 2024-09-09 05:56:11 | 0.00 | 1.64 | 4.72 | 0.00 |
| 2024-09-09 05:57:10 | 0.00 | 1.64 | 4.72 | 0.00 |
| 2024-09-09 05:58:10 | 0.41 | 1.64 | 5.13 | 0.00 |
| 2024-09-09 05:59:10 | 0.00 | 2.05 | 5.31 | 0.00 |
+---------------------+----------+
Above might contain errors as it has been calculated by hand. 24 hours is not complete.
To do this, you use a sum with a window frame. If you assume there is a single row every minute, for the 15 minute running total you can just use a range of 14 rows preceding:
select tid, regn1min,
sum(regn1min) over (order by tid rows between 14 preceding and current row) regn15min
And similarly for the 1 hour and 24 hours.
If you explicitly only want up to include 15 minutes, you need to use a range instead. mariadb only supports a range when ordering by a number, so you need to convert the times to a number (an offset in seconds from some fixed point):
select tid, regn1min,
sum(regn1min) over (order by timestampdiff(second,'2025-01-01 00:00:00',tid) range between 870 preceding and current row) regn15min
(870 seconds is 14 minutes 30 seconds, allowing for the slight discrepancies between your times).
In mysql you can use range with a datetime, like over (order by tid range between interval 870 second preceding and current row, but that's not yet possible in mariadb.
In either case, be aware that the sum is only over rows that are being selected (post grouping, if applicable). So if you want to select rows for just a range of times, say 1 week, but include in the rolling total the rows say 24 hours before, you need to expand the range to start 24 hours earlier, and wrap the whole thing as a subquery and in the outer query filter out the rows before your range start.