{"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":"main_data","compiledQueryString":"-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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","inputQueryString":"-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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","compiled":false,"inline":true},{"id":"summary_stats","compiledQueryString":"select\n round(min(lux), 1) as min_lux,\n round(max(lux), 1) as max_lux,\n round(avg(lux), 1) as avg_lux,\n round(min(proximity), 1) as min_proximity,\n round(max(proximity), 1) as max_proximity,\n round(avg(proximity), 1) as avg_proximity,\n min_lux || ' - ' || max_lux || ' lux' as lux_range,\n 'Avg: ' || avg_lux || ' lux' as lux_avg,\n min_proximity || ' - ' || max_proximity as proximity_range,\n 'Avg: ' || avg_proximity as proximity_avg,\n CASE\n WHEN avg_lux < 50 THEN 'bg-gray-50'\n WHEN avg_lux < 500 THEN 'bg-yellow-50'\n WHEN avg_lux < 1000 THEN 'bg-orange-50'\n ELSE 'bg-amber-50'\n END as lux_bg_color,\n CASE\n WHEN avg_proximity < 10 THEN 'bg-green-50'\n WHEN avg_proximity < 50 THEN 'bg-blue-50'\n ELSE 'bg-purple-50'\n END as proximity_bg_color\nfrom (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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)","inputQueryString":"select\n round(min(lux), 1) as min_lux,\n round(max(lux), 1) as max_lux,\n round(avg(lux), 1) as avg_lux,\n round(min(proximity), 1) as min_proximity,\n round(max(proximity), 1) as max_proximity,\n round(avg(proximity), 1) as avg_proximity,\n min_lux || ' - ' || max_lux || ' lux' as lux_range,\n 'Avg: ' || avg_lux || ' lux' as lux_avg,\n min_proximity || ' - ' || max_proximity as proximity_range,\n 'Avg: ' || avg_proximity as proximity_avg,\n CASE\n WHEN avg_lux < 50 THEN 'bg-gray-50'\n WHEN avg_lux < 500 THEN 'bg-yellow-50'\n WHEN avg_lux < 1000 THEN 'bg-orange-50'\n ELSE 'bg-amber-50'\n END as lux_bg_color,\n CASE\n WHEN avg_proximity < 10 THEN 'bg-green-50'\n WHEN avg_proximity < 50 THEN 'bg-blue-50'\n ELSE 'bg-purple-50'\n END as proximity_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(lux), 1) as avg_lux,\n round(avg(proximity), 1) as avg_proximity\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date >= '${inputs.date_filter.start}'::date\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date <= '${inputs.date_filter.end}'::date\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(lux), 1) as avg_lux,\n round(avg(proximity), 1) as avg_proximity\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date >= '${inputs.date_filter.start}'::date\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date <= '${inputs.date_filter.end}'::date\nGROUP BY hour_of_day\nORDER BY hour_of_day","compiled":false,"inline":true},{"id":"hourly_patterns_stacked","compiledQueryString":"-- Format data for the line chart with series\nSELECT\n hour_of_day,\n avg_lux as value,\n 'Light (lux)' as sensor_type\nFROM (-- Calculate hourly patterns throughout the day\nSELECT\n extract('hour' from timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) as hour_of_day,\n round(avg(lux), 1) as avg_lux,\n round(avg(proximity), 1) as avg_proximity\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date >= '${inputs.date_filter.start}'::date\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date <= '${inputs.date_filter.end}'::date\nGROUP BY hour_of_day\nORDER BY hour_of_day)\n\nUNION ALL\n\nSELECT\n hour_of_day,\n avg_proximity as value,\n 'Proximity' as sensor_type\nFROM (-- Calculate hourly patterns throughout the day\nSELECT\n extract('hour' from timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) as hour_of_day,\n round(avg(lux), 1) as avg_lux,\n round(avg(proximity), 1) as avg_proximity\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date >= '${inputs.date_filter.start}'::date\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date <= '${inputs.date_filter.end}'::date\nGROUP BY hour_of_day\nORDER BY hour_of_day)\nORDER BY hour_of_day, sensor_type","inputQueryString":"-- Format data for the line chart with series\nSELECT\n hour_of_day,\n avg_lux as value,\n 'Light (lux)' as sensor_type\nFROM ${hourly_patterns}\n\nUNION ALL\n\nSELECT\n hour_of_day,\n avg_proximity as value,\n 'Proximity' as sensor_type\nFROM ${hourly_patterns}\nORDER BY hour_of_day, sensor_type","compiled":true,"inline":true},{"id":"time_series_data","compiledQueryString":"-- Format data for the time series chart (30-minute intervals)\nWITH time_buckets AS (\n SELECT\n timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) as timestamp,\n -- Create 30-minute buckets by flooring to the hour and adding 0 or 30 minutes\n CASE\n WHEN EXTRACT(MINUTE FROM timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) < 30\n THEN date_trunc('hour', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp))\n ELSE date_trunc('hour', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) + INTERVAL '30 minutes'\n END AS half_hour_timestamp,\n lux,\n proximity\n FROM (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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)\n)\n\nSELECT\n half_hour_timestamp,\n round(avg(lux), 1) as value,\n 'Light (lux)' as sensor_type\nFROM time_buckets\nGROUP BY half_hour_timestamp\n\nUNION ALL\n\nSELECT\n half_hour_timestamp,\n round(avg(proximity), 1) as value,\n 'Proximity' as sensor_type\nFROM time_buckets\nGROUP BY half_hour_timestamp\nORDER BY half_hour_timestamp, sensor_type","inputQueryString":"-- Format data for the time series chart (30-minute intervals)\nWITH time_buckets AS (\n SELECT\n timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp) as timestamp,\n -- Create 30-minute buckets by flooring to the hour and adding 0 or 30 minutes\n CASE\n WHEN EXTRACT(MINUTE FROM timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) < 30\n THEN date_trunc('hour', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp))\n ELSE date_trunc('hour', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)) + INTERVAL '30 minutes'\n END AS half_hour_timestamp,\n lux,\n proximity\n FROM ${main_data}\n)\n\nSELECT\n half_hour_timestamp,\n round(avg(lux), 1) as value,\n 'Light (lux)' as sensor_type\nFROM time_buckets\nGROUP BY half_hour_timestamp\n\nUNION ALL\n\nSELECT\n half_hour_timestamp,\n round(avg(proximity), 1) as value,\n 'Proximity' as sensor_type\nFROM time_buckets\nGROUP BY half_hour_timestamp\nORDER BY half_hour_timestamp, sensor_type","compiled":true,"inline":true},{"id":"lux_extremes","compiledQueryString":"select\n timestamp,\n lux,\n case\n when lux = (select max(lux) from (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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))\n then 'Brightest: ' || round(lux, 1) || ' Lux'\n when lux = (select min(lux) from (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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))\n then 'Darkest: ' || round(lux, 1) || ' Lux'\n end as label\nfrom (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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)\nwhere lux = (select max(lux) from (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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))\n or lux = (select min(lux) from (-- Get the base light and proximity data we need\nselect\n timestamp,\n lux,\n proximity\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))","inputQueryString":"select\n timestamp,\n lux,\n case\n when lux = (select max(lux) from ${main_data})\n then 'Brightest: ' || round(lux, 1) || ' Lux'\n when lux = (select min(lux) from ${main_data})\n then 'Darkest: ' || round(lux, 1) || ' Lux'\n end as label\nfrom ${main_data}\nwhere lux = (select max(lux) from ${main_data})\n or lux = (select min(lux) from ${main_data})","compiled":true,"inline":true},{"id":"daily_values_for_calendar","compiledQueryString":"-- Get daily average light and proximity values for the calendar heatmaps\nSELECT\n strftime(date_trunc('day', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)), '%Y-%m-%d') as day,\n round(avg(lux), 1) as avg_lux,\n round(avg(proximity), 1) as avg_proximity\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date >= '${inputs.date_filter.start}'::date\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date <= '${inputs.date_filter.end}'::date\n AND (lux IS NOT NULL OR proximity IS NOT NULL)\nGROUP BY day\nORDER BY day","inputQueryString":"-- Get daily average light and proximity values for the calendar heatmaps\nSELECT\n strftime(date_trunc('day', timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)), '%Y-%m-%d') as day,\n round(avg(lux), 1) as avg_lux,\n round(avg(proximity), 1) as avg_proximity\nFROM all_stations\nWHERE station_id = '${params.station}'\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date >= '${inputs.date_filter.start}'::date\n AND timezone('${Intl.DateTimeFormat().resolvedOptions().timeZone}', timestamp)::date <= '${inputs.date_filter.end}'::date\n AND (lux IS NOT NULL OR proximity IS NOT NULL)\nGROUP BY day\nORDER BY day","compiled":false,"inline":true}]}

Light and Proximity

Enviro+ Outdoor New Cairo - Light & Proximity

Light and Proximity Summary

Hourly Patterns

Time Series Analysis

Daily Calendar Views