MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.5.17-MariaDB-1:10.5.17+maria~deb11


CREATE TABLE MyTable (
  ts datetime NOT NULL,
  Value decimal(10,4) NOT NULL
)
INSERT INTO `MyTable` (ts, Value) VALUES
('2022-06-03 05:00:00', '1'),
('2022-06-03 06:00:00', '2'),
('2022-06-03 07:00:00', '3'),
('2022-06-03 08:00:00', '4'),
('2022-06-03 09:00:00', '5'),
('2022-06-03 10:00:00', '6'),
('2022-06-04 05:00:00', '2'),
('2022-06-04 06:00:00', '5'),
('2022-06-04 07:00:00', '1'),
('2022-06-04 08:00:00', '3'),
('2022-06-04 09:00:00', '2'),
('2022-06-04 10:00:00', '5'),
('2022-06-05 05:00:00', '1'),
('2022-06-05 06:00:00', '1'),
('2022-06-05 07:00:00', '9'),
('2022-06-05 08:00:00', '3'),
('2022-06-05 09:00:00', '2'),
('2022-06-05 10:00:00', '1');

Records: 18  Duplicates: 0  Warnings: 0


select DATE_FORMAT(DATE_ADD(ts, INTERVAL 30 MINUTE),'%Y-%m-%d') as time, max(Value)
from MyTable
group by time


time
max(Value)


2022-06-03
6.0000

2022-06-04
5.0000

2022-06-05
9.0000


select DATE_FORMAT(DATE_ADD(ts, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00') as time, ROUND(sum(Value) over (order by time),2) as 'ValueA' 
from MyTable order by time


time
ValueA


2022-06-03 05:00
1.00

2022-06-03 06:00
3.00

2022-06-03 07:00
6.00

2022-06-03 08:00
10.00

2022-06-03 09:00
15.00

2022-06-03 10:00
21.00

2022-06-04 05:00
23.00

2022-06-04 06:00
28.00

2022-06-04 07:00
29.00

2022-06-04 08:00
32.00

2022-06-04 09:00
34.00

2022-06-04 10:00
39.00

2022-06-05 05:00
40.00

2022-06-05 06:00
41.00

2022-06-05 07:00
50.00

2022-06-05 08:00
53.00

2022-06-05 09:00
55.00

2022-06-05 10:00
56.00


select ts, value
     , first_value(date(ts)) over (order by ts) = date(ts) as init
     , sum(value) over (order by ts) as valueA
from MyTable 


ts
value
init
valueA


2022-06-03 05:00:00
1.0000
1
1.0000

2022-06-03 06:00:00
2.0000
1
3.0000

2022-06-03 07:00:00
3.0000
1
6.0000

2022-06-03 08:00:00
4.0000
1
10.0000

2022-06-03 09:00:00
5.0000
1
15.0000

2022-06-03 10:00:00
6.0000
1
21.0000

2022-06-04 05:00:00
2.0000
0
23.0000

2022-06-04 06:00:00
5.0000
0
28.0000

2022-06-04 07:00:00
1.0000
0
29.0000

2022-06-04 08:00:00
3.0000
0
32.0000

2022-06-04 09:00:00
2.0000
0
34.0000

2022-06-04 10:00:00
5.0000
0
39.0000

2022-06-05 05:00:00
1.0000
0
40.0000

2022-06-05 06:00:00
1.0000
0
41.0000

2022-06-05 07:00:00
9.0000
0
50.0000

2022-06-05 08:00:00
3.0000
0
53.0000

2022-06-05 09:00:00
2.0000
0
55.0000

2022-06-05 10:00:00
1.0000
0
56.0000


with classify (ts, value, init) as 
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
), first_day as ( 
  select ts, value, value as valueA
  from classify 
  where init = 1
), rem_days as (
  select ts, value, sum(value) over (order by ts) as valueA
  from classify
  where init = 0
)
select ts, value, valueA from first_day
union all
select ts, value, valueA + (select max(value) from first_day) from rem_days
order by ts
;



ts
value
valueA


2022-06-03 05:00:00
1.0000
1.0000

2022-06-03 06:00:00
2.0000
2.0000

2022-06-03 07:00:00
3.0000
3.0000

2022-06-03 08:00:00
4.0000
4.0000

2022-06-03 09:00:00
5.0000
5.0000

2022-06-03 10:00:00
6.0000
6.0000

2022-06-04 05:00:00
2.0000
8.0000

2022-06-04 06:00:00
5.0000
13.0000

2022-06-04 07:00:00
1.0000
14.0000

2022-06-04 08:00:00
3.0000
17.0000

2022-06-04 09:00:00
2.0000
19.0000

2022-06-04 10:00:00
5.0000
24.0000

2022-06-05 05:00:00
1.0000
25.0000

2022-06-05 06:00:00
1.0000
26.0000

2022-06-05 07:00:00
9.0000
35.0000

2022-06-05 08:00:00
3.0000
38.0000

2022-06-05 09:00:00
2.0000
40.0000

2022-06-05 10:00:00
1.0000
41.0000


with classify (ts, value, init) as 
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
)
select ts, value
     , case when init = 1 then value
                          else sum(value) over (order by ts) 
                          - (select sum(value) from classify where init = 1)
                          + (select max(value) from classify where init = 1)
       end as valueA
from classify
order by ts  
;


ts
value
valueA


2022-06-03 05:00:00
1.0000
1.0000

2022-06-03 06:00:00
2.0000
2.0000

2022-06-03 07:00:00
3.0000
3.0000

2022-06-03 08:00:00
4.0000
4.0000

2022-06-03 09:00:00
5.0000
5.0000

2022-06-03 10:00:00
6.0000
6.0000

2022-06-04 05:00:00
2.0000
8.0000

2022-06-04 06:00:00
5.0000
13.0000

2022-06-04 07:00:00
1.0000
14.0000

2022-06-04 08:00:00
3.0000
17.0000

2022-06-04 09:00:00
2.0000
19.0000

2022-06-04 10:00:00
5.0000
24.0000

2022-06-05 05:00:00
1.0000
25.0000

2022-06-05 06:00:00
1.0000
26.0000

2022-06-05 07:00:00
9.0000
35.0000

2022-06-05 08:00:00
3.0000
38.0000

2022-06-05 09:00:00
2.0000
40.0000

2022-06-05 10:00:00
1.0000
41.0000


with classify (ts, value, init) as 
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
)
select ts, value
     , case when init = 1 then value
                          else sum(value) over (partition by init order by ts)
                               + (select max(value) from classify where init = 1)
                          
       end as valueA
from classify
order by ts  
;


ts
value
valueA


2022-06-03 05:00:00
1.0000
1.0000

2022-06-03 06:00:00
2.0000
2.0000

2022-06-03 07:00:00
3.0000
3.0000

2022-06-03 08:00:00
4.0000
4.0000

2022-06-03 09:00:00
5.0000
5.0000

2022-06-03 10:00:00
6.0000
6.0000

2022-06-04 05:00:00
2.0000
8.0000

2022-06-04 06:00:00
5.0000
13.0000

2022-06-04 07:00:00
1.0000
14.0000

2022-06-04 08:00:00
3.0000
17.0000

2022-06-04 09:00:00
2.0000
19.0000

2022-06-04 10:00:00
5.0000
24.0000

2022-06-05 05:00:00
1.0000
25.0000

2022-06-05 06:00:00
1.0000
26.0000

2022-06-05 07:00:00
9.0000
35.0000

2022-06-05 08:00:00
3.0000
38.0000

2022-06-05 09:00:00
2.0000
40.0000

2022-06-05 10:00:00
1.0000
41.0000

time	ValueA
2022-06-03 05:00	1.00
2022-06-03 06:00	3.00
2022-06-03 07:00	6.00
2022-06-03 08:00	10.00
2022-06-03 09:00	15.00
2022-06-03 10:00	21.00
2022-06-04 05:00	23.00
2022-06-04 06:00	28.00
2022-06-04 07:00	29.00
2022-06-04 08:00	32.00
2022-06-04 09:00	34.00
2022-06-04 10:00	39.00
2022-06-05 05:00	40.00
2022-06-05 06:00	41.00
2022-06-05 07:00	50.00
2022-06-05 08:00	53.00
2022-06-05 09:00	55.00
2022-06-05 10:00	56.00

ts	value	init	valueA
2022-06-03 05:00:00	1.0000	1	1.0000
2022-06-03 06:00:00	2.0000	1	3.0000
2022-06-03 07:00:00	3.0000	1	6.0000
2022-06-03 08:00:00	4.0000	1	10.0000
2022-06-03 09:00:00	5.0000	1	15.0000
2022-06-03 10:00:00	6.0000	1	21.0000
2022-06-04 05:00:00	2.0000	0	23.0000
2022-06-04 06:00:00	5.0000	0	28.0000
2022-06-04 07:00:00	1.0000	0	29.0000
2022-06-04 08:00:00	3.0000	0	32.0000
2022-06-04 09:00:00	2.0000	0	34.0000
2022-06-04 10:00:00	5.0000	0	39.0000
2022-06-05 05:00:00	1.0000	0	40.0000
2022-06-05 06:00:00	1.0000	0	41.0000
2022-06-05 07:00:00	9.0000	0	50.0000
2022-06-05 08:00:00	3.0000	0	53.0000
2022-06-05 09:00:00	2.0000	0	55.0000
2022-06-05 10:00:00	1.0000	0	56.0000