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:01', 651729400)
-- missing 3 rows
,(27815 ,'2024.02.14 04:30:02', 651731800)
,(27816 ,'2024.02.14 04:38:02', 651731999)
,(27817 ,'2024.02.14 04:45:02', 651732200)
,(27818 ,'2024.02.14 05:15:02', 651732800)
;
select * from test;
Records: 19 Duplicates: 0 Warnings: 0
| 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:01 | 651729400 |
| 27815 | 2024-02-14 04:30:02 | 651731800 |
| 27816 | 2024-02-14 04:38:02 | 651731999 |
| 27817 | 2024-02-14 04:45:02 | 651732200 |
| 27818 | 2024-02-14 05:15:02 | 651732800 |
select id
,case when coalesce(n,0)>1 then date_add(Time,interval -(n-1)*15 minute) else Time end Time
,case when coalesce(n,0)>1 then Energy-(eDiff/td15mr)*(n-1) else energy end energy
,case when td15mr>1 then (eDiff/td15mr) else eDiff end cons15m
,n,td15mr
from(
select t1.id, t1.Time,t1.Energy
,round(time_to_sec(timediff(t1.time,coalesce(t2.time,t1.time)))/900) td15mr
,t1.Energy-coalesce(t2.Energy,t1.Energy) eDiff
from test t1
left join test t2 on (t2.id+1)=t1.id
)a
left join (select 1 n union all select 2 union all select 3
union all select 4 union all select 5)nn
on nn.n<=a.td15mr
| id | Time | energy | cons15m | n | td15mr |
|---|---|---|---|---|---|
| 27801 | 2024-02-13 23:45:00 | 651720672 | 624 | 1 | 1 |
| 27802 | 2024-02-14 00:15:02 | 651721917 | 622.5 | 1 | 2 |
| 27803 | 2024-02-14 00:30:00 | 651722540 | 623 | 1 | 1 |
| 27804 | 2024-02-14 00:45:00 | 651723129 | 589 | 1 | 1 |
| 27805 | 2024-02-14 01:00:02 | 651723769 | 640 | 1 | 1 |
| 27806 | 2024-02-14 01:15:01 | 651724405 | 636 | 1 | 1 |
| 27807 | 2024-02-14 01:30:01 | 651725030 | 625 | 1 | 1 |
| 27808 | 2024-02-14 02:00:01 | 651726275 | 622.5 | 1 | 2 |
| 27809 | 2024-02-14 02:15:02 | 651726880 | 605 | 1 | 1 |
| 27810 | 2024-02-14 02:30:01 | 651727519 | 639 | 1 | 1 |
| 27811 | 2024-02-14 02:45:00 | 651728130 | 611 | 1 | 1 |
| 27812 | 2024-02-14 03:00:02 | 651728751 | 621 | 1 | 1 |
| 27813 | 2024-02-14 03:15:02 | 651729381 | 630 | 1 | 1 |
| 27814 | 2024-02-14 03:29:01 | 651729400 | 19 | 1 | 1 |
| 27815 | 2024-02-14 04:30:02 | 651731800 | 600 | 1 | 4 |
| 27816 | 2024-02-14 04:38:02 | 651731999 | 199 | 1 | 1 |
| 27818 | 2024-02-14 05:15:02 | 651732800 | 300 | 1 | 2 |
| 27802 | 2024-02-14 00:00:02 | 651721294.5 | 622.5 | 2 | 2 |
| 27808 | 2024-02-14 01:45:01 | 651725652.5 | 622.5 | 2 | 2 |
| 27815 | 2024-02-14 04:15:02 | 651731200 | 600 | 2 | 4 |
| 27818 | 2024-02-14 05:00:02 | 651732500 | 300 | 2 | 2 |
| 27815 | 2024-02-14 04:00:02 | 651730600 | 600 | 3 | 4 |
| 27815 | 2024-02-14 03:45:02 | 651730000 | 600 | 4 | 4 |
| 27817 | 2024-02-14 04:45:02 | 651732200 | 201 | null | 0 |
| 27800 | 2024-02-13 23:30:01 | 651720048 | 0 | null | 0 |
select t1.id, t1.Time,t1.Energy,t1.cons15m,t1.energy-t2.energy cons1h
,t1.intN,t2.*
from(
select *
,cast(round(to_seconds(time)/900) as signed) intN
from(
select id
,case when coalesce(n,0)>1 then date_add(Time,interval -(n-1)*15 minute) else Time end Time
,case when coalesce(n,0)>1 then Energy-(eDiff/td15mr)*(n-1) else energy end energy
,case when td15mr>1 then (eDiff/td15mr) else eDiff end cons15m
,n,td15mr
from(
select t1.id, t1.Time,t1.Energy
,round(time_to_sec(timediff(t1.time,coalesce(t2.time,t1.time)))/900) td15mr
,t1.Energy-coalesce(t2.Energy,t1.Energy) eDiff
from test t1
left join test t2 on (t2.id+1)=t1.id
)a
left join (select 1 n union all select 2 union all select 3
union all select 4 union all select 5)nn
on nn.n<=a.td15mr
)b
)t1
left join
(
select *
,cast(round(to_seconds(time)/900) as signed) intN
from(
select id
,case when coalesce(n,0)>1 then date_add(Time,interval -(n-1)*15 minute) else Time end Time
,case when coalesce(n,0)>1 then Energy-(eDiff/td15mr)*(n-1) else energy end energy
,case when td15mr>1 then (eDiff/td15mr) else eDiff end cons15m
,n,td15mr
from(
select t1.id, t1.Time,t1.Energy
,round(time_to_sec(timediff(t1.time,coalesce(t2.time,t1.time)))/900) td15mr
| id | Time | energy | cons15m | cons1h | intN | id | Time | energy | cons15m | n | td15mr | intN |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 27800 | 2024-02-13 23:30:01 | 651720048 | 0 | null | 70972318 | null | null | null | null | null | null | null |
| 27801 | 2024-02-13 23:45:00 | 651720672 | 624 | null | 70972319 | null | null | null | null | null | null | null |
| 27802 | 2024-02-14 00:00:02 | 651721294.5 | 622.5 | null | 70972320 | null | null | null | null | null | null | null |
| 27802 | 2024-02-14 00:15:02 | 651721917 | 622.5 | null | 70972321 | null | null | null | null | null | null | null |
| 27803 | 2024-02-14 00:30:00 | 651722540 | 623 | 2492 | 70972322 | 27800 | 2024-02-13 23:30:01 | 651720048 | 0 | null | 0 | 70972318 |
| 27804 | 2024-02-14 00:45:00 | 651723129 | 589 | 2457 | 70972323 | 27801 | 2024-02-13 23:45:00 | 651720672 | 624 | 1 | 1 | 70972319 |
| 27805 | 2024-02-14 01:00:02 | 651723769 | 640 | 2474.5 | 70972324 | 27802 | 2024-02-14 00:00:02 | 651721294.5 | 622.5 | 2 | 2 | 70972320 |
| 27806 | 2024-02-14 01:15:01 | 651724405 | 636 | 2488 | 70972325 | 27802 | 2024-02-14 00:15:02 | 651721917 | 622.5 | 1 | 2 | 70972321 |
| 27807 | 2024-02-14 01:30:01 | 651725030 | 625 | 2490 | 70972326 | 27803 | 2024-02-14 00:30:00 | 651722540 | 623 | 1 | 1 | 70972322 |
| 27808 | 2024-02-14 01:45:01 | 651725652.5 | 622.5 | 2523.5 | 70972327 | 27804 | 2024-02-14 00:45:00 | 651723129 | 589 | 1 | 1 | 70972323 |
| 27808 | 2024-02-14 02:00:01 | 651726275 | 622.5 | 2506 | 70972328 | 27805 | 2024-02-14 01:00:02 | 651723769 | 640 | 1 | 1 | 70972324 |
| 27809 | 2024-02-14 02:15:02 | 651726880 | 605 | 2475 | 70972329 | 27806 | 2024-02-14 01:15:01 | 651724405 | 636 | 1 | 1 | 70972325 |
| 27810 | 2024-02-14 02:30:01 | 651727519 | 639 | 2489 | 70972330 | 27807 | 2024-02-14 01:30:01 | 651725030 | 625 | 1 | 1 | 70972326 |
| 27811 | 2024-02-14 02:45:00 | 651728130 | 611 | 2477.5 | 70972331 | 27808 | 2024-02-14 01:45:01 | 651725652.5 | 622.5 | 2 | 2 | 70972327 |
| 27812 | 2024-02-14 03:00:02 | 651728751 | 621 | 2476 | 70972332 | 27808 | 2024-02-14 02:00:01 | 651726275 | 622.5 | 1 | 2 | 70972328 |
| 27813 | 2024-02-14 03:15:02 | 651729381 | 630 | 2501 | 70972333 | 27809 | 2024-02-14 02:15:02 | 651726880 | 605 | 1 | 1 | 70972329 |
| 27814 | 2024-02-14 03:29:01 | 651729400 | 19 | 1881 | 70972334 | 27810 | 2024-02-14 02:30:01 | 651727519 | 639 | 1 | 1 | 70972330 |
| 27815 | 2024-02-14 03:45:02 | 651730000 | 600 | 1870 | 70972335 | 27811 | 2024-02-14 02:45:00 | 651728130 | 611 | 1 | 1 | 70972331 |
| 27815 | 2024-02-14 04:00:02 | 651730600 | 600 | 1849 | 70972336 | 27812 | 2024-02-14 03:00:02 | 651728751 | 621 | 1 | 1 | 70972332 |
| 27815 | 2024-02-14 04:15:02 | 651731200 | 600 | 1819 | 70972337 | 27813 | 2024-02-14 03:15:02 | 651729381 | 630 | 1 | 1 | 70972333 |
| 27815 | 2024-02-14 04:30:02 | 651731800 | 600 | 2400 | 70972338 | 27814 | 2024-02-14 03:29:01 | 651729400 | 19 | 1 | 1 | 70972334 |
| 27816 | 2024-02-14 04:38:02 | 651731999 | 199 | 1999 | 70972339 | 27815 | 2024-02-14 03:45:02 | 651730000 | 600 | 4 | 4 | 70972335 |
| 27817 | 2024-02-14 04:45:02 | 651732200 | 201 | 2200 | 70972339 | 27815 | 2024-02-14 03:45:02 | 651730000 | 600 | 4 | 4 | 70972335 |
| 27818 | 2024-02-14 05:00:02 | 651732500 | 300 | 1900 | 70972340 | 27815 | 2024-02-14 04:00:02 | 651730600 | 600 | 3 | 4 | 70972336 |
| 27818 | 2024-02-14 05:15:02 | 651732800 | 300 | 1600 | 70972341 | 27815 | 2024-02-14 04:15:02 | 651731200 | 600 | 2 | 4 | 70972337 |
select id,time,year(time)yy ,DAYOFYEAR(time) dd,hour(time)hh,minute(time) mi
,round((minute(time)+1)/15)*15 mi
,year(time)*10000000+DAYOFYEAR(time)*10000+hour(time)*100 +round((minute(time)+1)/15)*15 mi
,to_days(time) tdd
,round(to_seconds(time)/900) intN
from test
| id | time | yy | dd | hh | mi | mi | mi | tdd | intN |
|---|---|---|---|---|---|---|---|---|---|
| 27800 | 2024-02-13 23:30:01 | 2024 | 44 | 23 | 30 | 30 | 20240442330 | 739294 | 70972318 |
| 27801 | 2024-02-13 23:45:00 | 2024 | 44 | 23 | 45 | 45 | 20240442345 | 739294 | 70972319 |
| 27802 | 2024-02-14 00:15:02 | 2024 | 45 | 0 | 15 | 15 | 20240450015 | 739295 | 70972321 |
| 27803 | 2024-02-14 00:30:00 | 2024 | 45 | 0 | 30 | 30 | 20240450030 | 739295 | 70972322 |
| 27804 | 2024-02-14 00:45:00 | 2024 | 45 | 0 | 45 | 45 | 20240450045 | 739295 | 70972323 |
| 27805 | 2024-02-14 01:00:02 | 2024 | 45 | 1 | 0 | 0 | 20240450100 | 739295 | 70972324 |
| 27806 | 2024-02-14 01:15:01 | 2024 | 45 | 1 | 15 | 15 | 20240450115 | 739295 | 70972325 |
| 27807 | 2024-02-14 01:30:01 | 2024 | 45 | 1 | 30 | 30 | 20240450130 | 739295 | 70972326 |
| 27808 | 2024-02-14 02:00:01 | 2024 | 45 | 2 | 0 | 0 | 20240450200 | 739295 | 70972328 |
| 27809 | 2024-02-14 02:15:02 | 2024 | 45 | 2 | 15 | 15 | 20240450215 | 739295 | 70972329 |
| 27810 | 2024-02-14 02:30:01 | 2024 | 45 | 2 | 30 | 30 | 20240450230 | 739295 | 70972330 |
| 27811 | 2024-02-14 02:45:00 | 2024 | 45 | 2 | 45 | 45 | 20240450245 | 739295 | 70972331 |
| 27812 | 2024-02-14 03:00:02 | 2024 | 45 | 3 | 0 | 0 | 20240450300 | 739295 | 70972332 |
| 27813 | 2024-02-14 03:15:02 | 2024 | 45 | 3 | 15 | 15 | 20240450315 | 739295 | 70972333 |
| 27814 | 2024-02-14 03:29:01 | 2024 | 45 | 3 | 29 | 30 | 20240450330 | 739295 | 70972334 |
| 27815 | 2024-02-14 04:30:02 | 2024 | 45 | 4 | 30 | 30 | 20240450430 | 739295 | 70972338 |
| 27816 | 2024-02-14 04:38:02 | 2024 | 45 | 4 | 38 | 45 | 20240450445 | 739295 | 70972339 |
| 27817 | 2024-02-14 04:45:02 | 2024 | 45 | 4 | 45 | 45 | 20240450445 | 739295 | 70972339 |
| 27818 | 2024-02-14 05:15:02 | 2024 | 45 | 5 | 15 | 15 | 20240450515 | 739295 | 70972341 |
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) )
end cons1h
,td15mr,eDiff
from r
order by time;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive r as( select id, Time,Energy ,round(time_to_sec(timediff(lead(time,' at line 1
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;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't as( select * ,timediff(lead(time,1,time)over(order by time),time)tDiff ,le' at line 1