create table net_score (
date timestamp,
ip varchar,
up_score int
);
insert into net_score values
('2022-09-09 07:30:04.485979', '12.22.19.0', 51),
('2022-09-09 07:30:04.485979', '10.22.39.1', 95),
('2022-09-09 07:30:04.485979', '14.260.13.1', 100),
('2022-09-09 07:30:04.485979', '252.229.219.43', 97),
('2022-09-09 07:30:04.485979', '10.551.343.10', 97),
('2022-09-09 08:30:04.485979', '12.22.19.0', 11),
('2022-09-09 08:30:04.485979', '10.22.39.1', 54),
('2022-09-09 08:30:04.485979', '14.260.13.1', 89),
('2022-09-09 08:30:04.485979', '252.229.219.43', 37),
('2022-09-09 08:30:04.485979', '10.551.343.10', 11),
('2022-09-09 09:30:04.485979', '12.22.19.0', 54),
('2022-09-09 09:30:04.485979', '10.22.39.1', 15),
('2022-09-09 09:30:04.485979', '14.260.13.1', 90),
('2022-09-09 09:30:04.485979', '252.229.219.43', 17),
('2022-09-09 09:30:04.485979', '10.551.343.10', 50);
select
date_trunc('day', date) as daily, ip,
coalesce(sum(up_score) filter (where extract('hour' from date) = 6 ), 0) up_score_6,
sum(up_score) filter (where extract('hour' from date) = 7 ) up_score_7,
sum(up_score) filter (where extract('hour' from date) = 8 ) up_score_8,
sum(up_score) filter (where extract('hour' from date) = 9 ) up_score_9
from net_score
where date between '2022-09-09 05:30:00' and '2022-09-10 05:30:00'
group by ip, daily
order by ip, daily