{"queries":[{"id":"station_info","compiledQueryString":"SELECT * FROM station_registry WHERE station_id = '${params.station}'","inputQueryString":"SELECT * FROM station_registry WHERE station_id = '${params.station}'","compiled":false,"inline":true},{"id":"date_range_data","compiledQueryString":"select\n strftime(min(timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp))::TIMESTAMP, '%Y-%m-%d') as min_date,\n strftime(max(timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp))::TIMESTAMP, '%Y-%m-%d') as max_date\nfrom all_stations\nwhere station_id = '${params.station}'","inputQueryString":"select\n strftime(min(timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp))::TIMESTAMP, '%Y-%m-%d') as min_date,\n strftime(max(timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp))::TIMESTAMP, '%Y-%m-%d') as max_date\nfrom all_stations\nwhere station_id = '${params.station}'","compiled":false,"inline":true},{"id":"data_quality","compiledQueryString":"-- Detect damaged BME280 pressure readings.\n-- Operating range is 300 to 1100 hPa; anything outside is a fault.\nselect\n count(*) as total_rows,\n sum(case when pressure < 300 or pressure > 1100 then 1 else 0 end) as damaged_rows,\n round(100.0 * sum(case when pressure < 300 or pressure > 1100 then 1 else 0 end) / nullif(count(*), 0), 1) as damaged_pct,\n strftime(min(case when pressure < 300 or pressure > 1100 then timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) end)::TIMESTAMP, '%Y-%m-%d %H:%M') as first_damaged,\n strftime(max(case when pressure < 300 or pressure > 1100 then timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) end)::TIMESTAMP, '%Y-%m-%d %H:%M') as last_damaged\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and pressure is not null","inputQueryString":"-- Detect damaged BME280 pressure readings.\n-- Operating range is 300 to 1100 hPa; anything outside is a fault.\nselect\n count(*) as total_rows,\n sum(case when pressure < 300 or pressure > 1100 then 1 else 0 end) as damaged_rows,\n round(100.0 * sum(case when pressure < 300 or pressure > 1100 then 1 else 0 end) / nullif(count(*), 0), 1) as damaged_pct,\n strftime(min(case when pressure < 300 or pressure > 1100 then timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) end)::TIMESTAMP, '%Y-%m-%d %H:%M') as first_damaged,\n strftime(max(case when pressure < 300 or pressure > 1100 then timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) end)::TIMESTAMP, '%Y-%m-%d %H:%M') as last_damaged\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and pressure is not null","compiled":false,"inline":true},{"id":"main_data","compiledQueryString":"-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100","inputQueryString":"-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100","compiled":false,"inline":true},{"id":"summary_stats","compiledQueryString":"select\n round(min(pressure), 1) as min_pressure,\n round(max(pressure), 1) as max_pressure,\n round(avg(pressure), 1) as avg_pressure,\n concat_ws(' ', coalesce(min_pressure::varchar, '—'), '-', coalesce(max_pressure::varchar, '—'), 'hPa') as pressure_range,\n 'Avg: ' || coalesce(avg_pressure::varchar, '—') || ' hPa' as pressure_avg,\n CASE\n WHEN avg_pressure < 1000 THEN 'bg-blue-50'\n WHEN avg_pressure > 1025 THEN 'bg-orange-50'\n ELSE 'bg-green-50'\n END as pressure_bg_color\nfrom (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100)","inputQueryString":"select\n round(min(pressure), 1) as min_pressure,\n round(max(pressure), 1) as max_pressure,\n round(avg(pressure), 1) as avg_pressure,\n concat_ws(' ', coalesce(min_pressure::varchar, '—'), '-', coalesce(max_pressure::varchar, '—'), 'hPa') as pressure_range,\n 'Avg: ' || coalesce(avg_pressure::varchar, '—') || ' hPa' as pressure_avg,\n CASE\n WHEN avg_pressure < 1000 THEN 'bg-blue-50'\n WHEN avg_pressure > 1025 THEN 'bg-orange-50'\n ELSE 'bg-green-50'\n END as pressure_bg_color\nfrom ${main_data}","compiled":true,"inline":true},{"id":"hourly_patterns","compiledQueryString":"-- Calculate hourly patterns throughout the day\nSELECT\n extract('hour' from timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) as hour_of_day,\n round(avg(pressure), 1) as avg_pressure\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n AND coalesce(pressure, 1013) between 300 and 1100\nGROUP BY hour_of_day\nORDER BY hour_of_day","inputQueryString":"-- Calculate hourly patterns throughout the day\nSELECT\n extract('hour' from timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) as hour_of_day,\n round(avg(pressure), 1) as avg_pressure\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n AND coalesce(pressure, 1013) between 300 and 1100\nGROUP BY hour_of_day\nORDER BY hour_of_day","compiled":false,"inline":true},{"id":"time_series_data","compiledQueryString":"-- all_stations is already hourly-averaged, so select directly.\nSELECT\n timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) as local_timestamp,\n pressure as avg_pressure\nFROM (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100)\nORDER BY local_timestamp","inputQueryString":"-- all_stations is already hourly-averaged, so select directly.\nSELECT\n timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) as local_timestamp,\n pressure as avg_pressure\nFROM ${main_data}\nORDER BY local_timestamp","compiled":true,"inline":true},{"id":"pressure_extremes","compiledQueryString":"select\n timestamp,\n pressure,\n case\n when pressure = (select max(pressure) from (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100))\n then 'Highest: ' || round(pressure, 1) || ' hPa'\n when pressure = (select min(pressure) from (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100))\n then 'Lowest: ' || round(pressure, 1) || ' hPa'\n end as label\nfrom (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100)\nwhere pressure = (select max(pressure) from (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100))\n or pressure = (select min(pressure) from (-- Get the base pressure data we need, filtering out damaged rows.\n-- BME280 operating range is 300 to 1100 hPa.\nselect\n timestamp,\n pressure\nfrom all_stations\nwhere station_id = '${params.station}'\n and timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n and coalesce(pressure, 1013) between 300 and 1100))","inputQueryString":"select\n timestamp,\n pressure,\n case\n when pressure = (select max(pressure) from ${main_data})\n then 'Highest: ' || round(pressure, 1) || ' hPa'\n when pressure = (select min(pressure) from ${main_data})\n then 'Lowest: ' || round(pressure, 1) || ' hPa'\n end as label\nfrom ${main_data}\nwhere pressure = (select max(pressure) from ${main_data})\n or pressure = (select min(pressure) from ${main_data})","compiled":true,"inline":true},{"id":"daily_pressure_for_calendar","compiledQueryString":"-- Get daily average pressure values for the calendar heatmap\nSELECT\n strftime(date_trunc('day', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)), '%Y-%m-%d') as day,\n round(avg(pressure), 1) as avg_pressure\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n AND pressure IS NOT NULL\n AND pressure between 300 and 1100\nGROUP BY day\nORDER BY day","inputQueryString":"-- Get daily average pressure values for the calendar heatmap\nSELECT\n strftime(date_trunc('day', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)), '%Y-%m-%d') as day,\n round(avg(pressure), 1) as avg_pressure\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date between '${inputs.date_filter.start}'::date and '${inputs.date_filter.end}'::date + INTERVAL '1 day'\n AND pressure IS NOT NULL\n AND pressure between 300 and 1100\nGROUP BY day\nORDER BY day","compiled":false,"inline":true}]}

Atmospheric Pressure

Data for Canada Open Sensor 01 (d4c-opensensor-01) - Atmospheric Pressure

Pressure Summary

Hourly Pressure Patterns

Pressure Time Series

Daily Calendar View

Daily Atmospheric Pressure