SQLize Online / PHPize Online  /  SQLtest Online

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