...
SELECT * FROM crosstab(
$$SELECT
date_part('week',submitted),
h.a as hour,
sum((duration+500)/1000) as seconds
FROM weave.wv_statistics
FULL JOIN (select unnest((array[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23])) as a) h ON h.a = date_part('hour',submitted)
where date_part('week',submitted) > 0
group by 1,2
order by 1$$,
$$ SELECT m FROM generate_series(0,23) m $$
) as ( week int, "0" int, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int, "10" int, "11" int, "12"
int, "13" int, "14" int, "15" int, "16" int, "17" int, "18" int, "19" int, "20" int, "21" int, "22" int, "23" int);
Pivot table with column week and weekdays showing server usage in seconds
SELECT * FROM crosstab(
$$SELECT
date_part('week',submitted)::int,
date_part('isodow',submitted)::int as weekday,
sum((duration+500)/1000) as seconds
FROM weave.wv_statistics
group by 1,2
order by 1$$,
$$ SELECT m FROM generate_series(1,7) m $$
) as ( week int, "Måndag" int, "Tisdag" int, "Onsdag" int, "Torsdag" int, "Fredag" int, "Lördag" int, "Söndag" int );