MariaDB 10.5

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

select version();


version()


10.5.13-MariaDB


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');
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

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