By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (id int,Time datetime, Energy double);
insert into test values
(27800 ,'2024.02.13 23:30:01', 651720048)
,(27801 ,'2024.02.13 23:45:00', 651720672)
-- (missing)
,(27802 ,'2024.02.14 00:15:02', 651721917)
,(27803 ,'2024.02.14 00:30:00', 651722540)
,(27804 ,'2024.02.14 00:45:00', 651723129)
,(27805 ,'2024.02.14 01:00:02', 651723769)
,(27806 ,'2024.02.14 01:15:01', 651724405)
,(27807 ,'2024.02.14 01:30:01', 651725030)
-- (missing)
,(27808 ,'2024.02.14 02:00:01', 651726275)
,(27809 ,'2024.02.14 02:15:02', 651726880)
,(27810 ,'2024.02.14 02:30:01', 651727519)
,(27811 ,'2024.02.14 02:45:00', 651728130)
,(27812 ,'2024.02.14 03:00:02', 651728751)
,(27813 ,'2024.02.14 03:15:02', 651729381)
,(27814 ,'2024.02.14 03:29:58', 651729400)
,(27815 ,'2024.02.14 04:30:02', 651731800)
;
select * from test;
Records: 16 Duplicates: 0 Warnings: 16
| id | Time | Energy |
|---|---|---|
| 27800 | 2024-02-13 23:30:01 | 651720048 |
| 27801 | 2024-02-13 23:45:00 | 651720672 |
| 27802 | 2024-02-14 00:15:02 | 651721917 |
| 27803 | 2024-02-14 00:30:00 | 651722540 |
| 27804 | 2024-02-14 00:45:00 | 651723129 |
| 27805 | 2024-02-14 01:00:02 | 651723769 |
| 27806 | 2024-02-14 01:15:01 | 651724405 |
| 27807 | 2024-02-14 01:30:01 | 651725030 |
| 27808 | 2024-02-14 02:00:01 | 651726275 |
| 27809 | 2024-02-14 02:15:02 | 651726880 |
| 27810 | 2024-02-14 02:30:01 | 651727519 |
| 27811 | 2024-02-14 02:45:00 | 651728130 |
| 27812 | 2024-02-14 03:00:02 | 651728751 |
| 27813 | 2024-02-14 03:15:02 | 651729381 |
| 27814 | 2024-02-14 03:29:58 | 651729400 |
| 27815 | 2024-02-14 04:30:02 | 651731800 |
with recursive r as(
select id, Time,Energy
,round(time_to_sec(timediff(lead(time,1,time)over(order by time),time))/900) td15mr
,lead(Energy,1,Energy)over(order by time)-Energy eDiff
from test
union all
select id, addtime(Time,'00:15:00'),Energy+(eDiff/td15mr)
,td15mr-1 as td15mr
,eDiff-(eDiff/td15mr)
from r where td15mr>1
)
select id, Time,Energy
,Energy-lag(Energy,1,Energy)over(order by time) cons15m
,case when minute(date_add(time,INTERVAL 1 minute))<15 then
energy-(min(energy)over(order by time rows between 4 preceding and current row) )
-- min(energy)over(order by time rows between 1 preceding and current row)
end cons1h
,td15mr,eDiff
from r
order by time;
| id | Time | Energy | cons15m | cons1h | td15mr | eDiff |
|---|---|---|---|---|---|---|
| 27800 | 2024-02-13 23:30:01 | 651720048 | 0 | null | 1 | 624 |
| 27801 | 2024-02-13 23:45:00 | 651720672 | 624 | null | 2 | 1245 |
| 27801 | 2024-02-14 00:00:00 | 651721294.5 | 622.5 | 1246.5 | 1 | 622.5 |
| 27802 | 2024-02-14 00:15:02 | 651721917 | 622.5 | null | 1 | 623 |
| 27803 | 2024-02-14 00:30:00 | 651722540 | 623 | null | 1 | 589 |
| 27804 | 2024-02-14 00:45:00 | 651723129 | 589 | null | 1 | 640 |
| 27805 | 2024-02-14 01:00:02 | 651723769 | 640 | 2474.5 | 1 | 636 |
| 27806 | 2024-02-14 01:15:01 | 651724405 | 636 | null | 1 | 625 |
| 27807 | 2024-02-14 01:30:01 | 651725030 | 625 | null | 2 | 1245 |
| 27807 | 2024-02-14 01:45:01 | 651725652.5 | 622.5 | null | 1 | 622.5 |
| 27808 | 2024-02-14 02:00:01 | 651726275 | 622.5 | 2506 | 1 | 605 |
| 27809 | 2024-02-14 02:15:02 | 651726880 | 605 | null | 1 | 639 |
| 27810 | 2024-02-14 02:30:01 | 651727519 | 639 | null | 1 | 611 |
| 27811 | 2024-02-14 02:45:00 | 651728130 | 611 | null | 1 | 621 |
| 27812 | 2024-02-14 03:00:02 | 651728751 | 621 | 2476 | 1 | 630 |
| 27813 | 2024-02-14 03:15:02 | 651729381 | 630 | null | 1 | 19 |
| 27814 | 2024-02-14 03:29:58 | 651729400 | 19 | null | 4 | 2400 |
| 27814 | 2024-02-14 03:44:58 | 651730000 | 600 | null | 3 | 1800 |
| 27814 | 2024-02-14 03:59:58 | 651730600 | 600 | 1849 | 2 | 1200 |
| 27814 | 2024-02-14 04:14:58 | 651731200 | 600 | null | 1 | 600 |
| 27815 | 2024-02-14 04:30:02 | 651731800 | 600 | null | 0 | 0 |
with t as(
select *
,timediff(lead(time,1,time)over(order by time),time)tDiff
,lead(Energy,1,Energy)over(order by time)-Energy eDiff
,lead(Energy,1,Energy)over(order by time) nextVal
,lead(time,1,time)over(order by time) nextTime
from test
)
select *
,time_to_sec(tDiff) tds
,time_to_sec(tDiff)/900 td15m
,round(time_to_sec(tDiff)/900) td15mr
from t;
| id | Time | Energy | tDiff | eDiff | nextVal | nextTime | tds | td15m | td15mr |
|---|---|---|---|---|---|---|---|---|---|
| 27800 | 2024-02-13 23:30:01 | 651720048 | 00:14:59 | 624 | 651720672 | 2024-02-13 23:45:00 | 899 | 0.9989 | 1 |
| 27801 | 2024-02-13 23:45:00 | 651720672 | 00:30:02 | 1245 | 651721917 | 2024-02-14 00:15:02 | 1802 | 2.0022 | 2 |
| 27802 | 2024-02-14 00:15:02 | 651721917 | 00:14:58 | 623 | 651722540 | 2024-02-14 00:30:00 | 898 | 0.9978 | 1 |
| 27803 | 2024-02-14 00:30:00 | 651722540 | 00:15:00 | 589 | 651723129 | 2024-02-14 00:45:00 | 900 | 1.0000 | 1 |
| 27804 | 2024-02-14 00:45:00 | 651723129 | 00:15:02 | 640 | 651723769 | 2024-02-14 01:00:02 | 902 | 1.0022 | 1 |
| 27805 | 2024-02-14 01:00:02 | 651723769 | 00:14:59 | 636 | 651724405 | 2024-02-14 01:15:01 | 899 | 0.9989 | 1 |
| 27806 | 2024-02-14 01:15:01 | 651724405 | 00:15:00 | 625 | 651725030 | 2024-02-14 01:30:01 | 900 | 1.0000 | 1 |
| 27807 | 2024-02-14 01:30:01 | 651725030 | 00:30:00 | 1245 | 651726275 | 2024-02-14 02:00:01 | 1800 | 2.0000 | 2 |
| 27808 | 2024-02-14 02:00:01 | 651726275 | 00:15:01 | 605 | 651726880 | 2024-02-14 02:15:02 | 901 | 1.0011 | 1 |
| 27809 | 2024-02-14 02:15:02 | 651726880 | 00:14:59 | 639 | 651727519 | 2024-02-14 02:30:01 | 899 | 0.9989 | 1 |
| 27810 | 2024-02-14 02:30:01 | 651727519 | 00:14:59 | 611 | 651728130 | 2024-02-14 02:45:00 | 899 | 0.9989 | 1 |
| 27811 | 2024-02-14 02:45:00 | 651728130 | 00:15:02 | 621 | 651728751 | 2024-02-14 03:00:02 | 902 | 1.0022 | 1 |
| 27812 | 2024-02-14 03:00:02 | 651728751 | 00:15:00 | 630 | 651729381 | 2024-02-14 03:15:02 | 900 | 1.0000 | 1 |
| 27813 | 2024-02-14 03:15:02 | 651729381 | 00:14:56 | 19 | 651729400 | 2024-02-14 03:29:58 | 896 | 0.9956 | 1 |
| 27814 | 2024-02-14 03:29:58 | 651729400 | 01:00:04 | 2400 | 651731800 | 2024-02-14 04:30:02 | 3604 | 4.0044 | 4 |
| 27815 | 2024-02-14 04:30:02 | 651731800 | 00:00:00 | 0 | 651731800 | 2024-02-14 04:30:02 | 0 | 0.0000 | 0 |