--all times - all museums select --distinct tablewithcounts.project_name tablewithcounts.freq_val, count(tablewithcounts.id) --tablewithcounts.id, tablewithcounts.project_name, tablewithcounts.metric_name, tablewithcounts."time", tablewithcounts."Day", tablewithcounts."Hour", tablewithcounts.value, tablewithcounts.freq_val, tablewithcounts.display_value --, geometry_id from ( SELECT id, project_name, metric_name, "time", value, display_value, geometry_id, round(cast(value as numeric), 1) as freq_val, extract(hour from "time") as "Hour", to_char("time", 'Day') as "Day" FROM public.metrics_app_timeandgeometric_historic --where project_name in ('Lasswade') where ( project_name = 'Roman_Baths' --or project_name = 'Mauricewood_School' --or project_name = 'Eaglesham_Primaryschool' or project_name in ( 'Roman_Baths' ) ) and metric_name = 'temp' and (value >= 0 and value < 50) and display_value is NULL ) as tablewithcounts -- where -- --tablewithcounts."Day" not in ('Saturday ', 'Sunday ') -- tablewithcounts."Day" not like '%Saturday%' -- and tablewithcounts."Day" not like '%Sunday%' -- and tablewithcounts."Hour" >= 8 and tablewithcounts."Hour" < 16 -- and tablewithcounts.time not between '2021-08-01' and '2021-08-17' -- and tablewithcounts.time not between '2021-09-20' and '2021-09-20' -- and tablewithcounts.time not between '2021-10-18' and '2021-10-25' -- and tablewithcounts.time not between '2021-12-23' and '2022-01-10' -- and tablewithcounts.time not between '2022-02-14' and '2022-02-18' -- and tablewithcounts.time not between '2022-04-11' and '2022-04-22' -- and tablewithcounts.time not between '2022-05-02' and '2022-05-02' -- and tablewithcounts.time not between '2022-05-23' and '2022-05-23' -- and tablewithcounts.time not between '2022-07-01' and '2022-07-31' -- and tablewithcounts.time not between '2022-08-01' and '2022-08-16' -- and tablewithcounts.time not between '2022-09-16' and '2022-09-19' -- and tablewithcounts.time not between '2022-10-17' and '2022-10-24' -- and tablewithcounts.time not between '2022-12-21' and '2023-01-04' -- and tablewithcounts.time not between '2023-02-13' and '2023-02-17' -- and tablewithcounts.time not between '2023-04-03' and '2023-04-14' -- and tablewithcounts.time not between '2023-05-01' and '2023-05-01' -- and tablewithcounts.time not between '2023-05-22' and '2023-05-22' -- and tablewithcounts.time not between '2023-06-29' and '2023-07-31' group by tablewithcounts.freq_val order by tablewithcounts.freq_val --limit 1000
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter