By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (interval_date date, interval_time char(5), power decimal(5,2), on_status char(1))
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:00',2.65,'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:05', 3.92, 'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:10', 2.05, 'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:15', 1.85, 'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:20', 5.92, 'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:25', 7.52, 'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:30', 9.84, 'Y');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:35', 6.84, 'N');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:40', 5.01, 'N');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:45', 4.70, 'N');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:50', 8.57, 'N');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','00:55', 1.94, 'N');
INSERT INTO mytable (interval_date, interval_time, power, on_status) VALUES ('2022-05-01','01:00', 3.87, 'Y');
13 rows affected
SELECT interval_date,
CASE WHEN SUBSTRING(interval_time,4,2)='00' THEN interval_time
WHEN SUBSTRING(interval_time,1,2)='23' THEN '00:00'
ELSE FORMAT(convert(int,SUBSTRING(interval_time,1,2))+1,'00')+':00'
END interval_time,
AVG(power)
FROM mytable
-- WHERE on_status = 'Y'
GROUP BY interval_date,
CASE WHEN SUBSTRING(interval_time,4,2)='00' THEN interval_time
WHEN SUBSTRING(interval_time,1,2)='23' THEN '00:00'
ELSE FORMAT(convert(int,SUBSTRING(interval_time,1,2))+1,'00')+':00'
END
| interval_date | interval_time | (No column name) |
|---|---|---|
| 2022-05-01 | 00:00 | 2.650000 |
| 2022-05-01 | 01:00 | 5.169166 |