def interconnector_power_flow(time_series: TimeSeries, network_region: str) -> str: """Get interconnector region flows using materialized view""" ___query = """ select time_bucket_gapfill(INTERVAL '5 minutes', bs.trading_interval) as trading_interval, bs.network_region, case when max(bs.net_interchange) < 0 then max(bs.net_interchange) else 0 end as imports, case when max(bs.net_interchange) > 0 then max(bs.net_interchange) else 0 end as exports from balancing_summary bs where bs.network_id = '{network_id}' and bs.network_region= '{region}' and bs.trading_interval <= '{date_end}' and bs.trading_interval >= '{date_start}' group by 1, 2 order by trading_interval desc; """.format( network_id=time_series.network.code, region=network_region, date_start=time_series.get_range().start, date_end=time_series.get_range().end, ) return dedent(___query)
def emission_factor_region_query( time_series: TimeSeries, network_region_code: Optional[str] = None) -> str: __query = """ select f.trading_interval at time zone '{timezone}' as ti, f.network_region, avg(f.emissions_per_mw) * 2 from mv_region_emissions_45d f where f.network_id='{network_id}' and {network_region_query} f.trading_interval <= '{date_max}' and f.trading_interval >= '{date_min}' group by 1, 2 order by 1 asc; """ network_region_query = "" if network_region_code: network_region_query = f"f.network_region='{network_region_code}' and" date_range = time_series.get_range() query = dedent( __query.format( network_region_query=network_region_query, network_id=time_series.network.code, trunc=time_series.interval.trunc, date_max=date_range.end, date_min=date_range.start, timezone=time_series.network.timezone_database, )) return query
def energy_facility_query(time_series: TimeSeries, facility_codes: List[str]) -> str: """ Get Energy for a list of facility codes """ __query = """ select date_trunc('{trunc}', t.trading_day at time zone '{timezone}') as trading_day, t.facility_code, sum(t.energy) as fueltech_energy, sum(t.market_value) as fueltech_market_value, sum(t.emissions) as fueltech_emissions from at_facility_daily t where t.trading_day <= '{date_max}' and t.trading_day >= '{date_min}' and t.facility_code in ({facility_codes_parsed}) group by 1, 2 order by trading_day desc; """ date_range = time_series.get_range() query = dedent( __query.format( facility_codes_parsed=duid_in_case(facility_codes), trunc=time_series.interval.trunc, date_max=date_range.end, date_min=date_range.start, timezone=time_series.network.timezone_database, )) return query
def network_fueltech_demand_query(time_series: TimeSeries) -> str: __query = """ select f.fueltech_id, round(sum(fs.eoi_quantity) / 1000, 2) as energy, sum(bs.demand_total) as demand from facility_scada fs left join balancing_summary bs on bs.trading_interval = fs.trading_interval and bs.network_id = fs.network_id left join facility f on fs.facility_code = f.code join fueltech ft on f.fueltech_id = ft.code where fs.trading_interval >= '{date_min}' and fs.trading_interval < '{date_max}' and fs.network_id = '{network_id}' and f.dispatch_type = 'GENERATOR' group by 1; """ date_range = time_series.get_range() date_min: datetime = date_range.end - timedelta(days=1) query = dedent( __query.format( network_id=time_series.network.code, trunc=time_series.interval.trunc, date_max=date_range.end, date_min=date_min, timezone=time_series.network.timezone_database, )) return query
def demand_network_region_query(time_series: TimeSeries, network_region: str | None, networks: list[NetworkSchema] = []) -> str: """Get the network demand energy and market_value""" if not networks: networks = [time_series.network] if time_series.network not in networks: networks.append(time_series.network) ___query = """ select date_trunc('{trunc}', trading_day) as trading_day, network_id, {network_region_select} round(sum(demand_energy), 4), round(sum(demand_market_value), 4) from at_network_demand where {network_query} {network_region} trading_day >= '{date_min}'::date and trading_day <= '{date_max}'::date group by 1,2 {group_by} order by 1 asc """ network_region_query = "" network_region_select = f"'{time_series.network.code}' as network_region," group_by = "" if network_region: network_region_query = f"network_region='{network_region}' and" network_region_select = "network_region," group_by = ",3" date_range = time_series.get_range() networks_list = networks_to_in(networks) network_query = "network_id IN ({}) and ".format(networks_list) query = dedent( ___query.format( trunc=time_series.interval.trunc, date_min=date_range.start.date(), date_max=date_range.end.date(), network_id=time_series.network.code, network_region=network_region_query, network_region_select=network_region_select, group_by=group_by, network_query=network_query, )) return query
def energy_network_fueltech_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, coalesce_with: Optional[int] = 0, ) -> str: """ Get Energy for a network or network + region based on a year """ if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select date_trunc('{trunc}', t.trading_day), t.fueltech_id, coalesce(sum(t.energy) / 1000, {coalesce_with}) as fueltech_energy, coalesce(sum(t.market_value), {coalesce_with}) as fueltech_market_value, coalesce(sum(t.emissions), {coalesce_with}) as fueltech_emissions from at_facility_daily t left join facility f on t.facility_code = f.code where t.trading_day <= '{date_max}'::date and t.trading_day >= '{date_min}'::date and t.fueltech_id not in ('imports', 'exports', 'interconnector') and {network_query} {network_region_query} 1=1 group by 1, 2 order by 1 desc; """ network_region_query = "" date_range = time_series.get_range() if network_region: network_region_query = f"f.network_region='{network_region}' and" networks_list = networks_to_in(networks_query) network_query = "t.network_id IN ({}) and ".format(networks_list) query = dedent( __query.format( trunc=date_range.interval.trunc, date_min=date_range.start.date(), date_max=date_range.end.date(), network_query=network_query, network_region_query=network_region_query, coalesce_with=coalesce_with, )) return query
def price_network_query( time_series: TimeSeries, group_field: str = "bs.network_id", network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select time_bucket_gapfill('{trunc}', bs.trading_interval) as trading_interval, {group_field}, avg(bs.price) as price from balancing_summary bs where bs.trading_interval <= '{date_max}' and bs.trading_interval >= '{date_min}' and {network_query} {network_region_query} 1=1 group by 1, 2 order by 1 desc """ timezone = time_series.network.timezone_database network_region_query = "" if network_region: network_region_query = f"bs.network_region='{network_region}' and " group_field = "bs.network_region" network_query = "bs.network_id IN ({}) and ".format( networks_to_in(networks_query)) if len(networks_query) > 1: group_field = "'AU'" date_max = time_series.get_range().end date_min = time_series.get_range().start query = dedent( __query.format( network_query=network_query, trunc=time_series.interval.interval_sql, network_region_query=network_region_query, timezone=timezone, date_max=date_max, date_min=date_min, group_field=group_field, )) return query
def network_demand_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select trading_interval at time zone '{timezone}', network_id, max(demand_total) as demand from balancing_summary bs where bs.trading_interval <= '{date_max}' and bs.trading_interval >= '{date_min}' and {network_query} {network_region_query} 1=1 group by 1, {groups_additional} order by 1 desc; """ group_keys = ["network_id"] network_region_query = "" if network_region: group_keys.append("network_region") network_region_query = f"bs.network_region = '{network_region}' and " groups_additional = ", ".join(group_keys) network_query = "bs.network_id IN ({}) and ".format( networks_to_in(networks_query)) date_max = time_series.get_range().end date_min = time_series.get_range().start query = __query.format( timezone=time_series.network.timezone_database, date_max=date_max, date_min=date_min, network_id=time_series.network.code, network_query=network_query, network_region_query=network_region_query, groups_additional=groups_additional, ) return dedent(query)
def energy_network_interconnector_emissions_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: """ Get emissions for a network or network + region based on a year """ if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select t.trading_interval at time zone '{timezone}' as trading_interval, t.flow_from, t.flow_to, t.flow_energy as energy, t.flow_from_emissions, t.flow_to_emissions from vw_region_flow_emissions t where t.trading_interval <= '{date_max}' and t.trading_interval >= '{date_min}' and {network_region_query} 1=1 order by 1 desc """ timezone = time_series.network.timezone_database network_region_query = "" date_range = time_series.get_range() if network_region: network_region_query = f""" (t.flow_from='{network_region}' or t.flow_to='{network_region}') and """ query = dedent( __query.format( timezone=timezone, # trunc=date_range.interval.trunc, date_min=date_range.start, date_max=date_range.end, network_region_query=network_region_query, ) ) return query
def get_date_range(network: NetworkSchema) -> DatetimeRange: date_range = get_scada_range(network=NetworkNEM) time_series = TimeSeries( start=date_range.start, end=date_range.end, interval=human_to_interval("1d"), period=human_to_period("all"), network=network, ) return time_series.get_range()
def energy_network_flow_query( time_series: TimeSeries, network_region: str, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: """ Get emissions for a network or network + region based on a year """ if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select date_trunc('{trunc}', t.trading_interval) as trading_interval, sum(t.imports_energy), sum(t.exports_energy), abs(sum(t.imports_market_value_rrp)), abs(sum(t.exports_market_value_rrp)) from ( select ei.trading_interval, ei.imports_energy, ei.exports_energy, ei.imports_market_value, ei.exports_market_value, ei.imports_market_value_rrp, ei.exports_market_value_rrp from mv_interchange_energy_nem_region ei where ei.trading_interval <= '{date_max}' and ei.trading_interval >= '{date_min}' and ei.network_region='{network_region}' ) as t group by 1 order by 1 desc """ date_range = time_series.get_range() query = dedent( __query.format( network_region=network_region, trunc=date_range.interval.trunc, date_min=date_range.start, date_max=date_range.end, )) return query
def interconnector_flow_network_regions_query( time_series: TimeSeries, network_region: Optional[str] = None) -> str: """ """ __query = """ select fs.trading_interval at time zone '{timezone}' as trading_interval, f.network_region || '->' || f.interconnector_region_to as flow_region, f.network_region, f.interconnector_region_to, sum(fs.generated) as flow_power from facility_scada fs left join facility f on fs.facility_code = f.code where f.interconnector is True and f.network_id='{network_id}' and fs.trading_interval <= '{date_end}' and fs.trading_interval >= '{date_start}' {region_query} group by 1, 2, 3, 4 order by 1 desc, 2 asc """ region_query = "" if network_region: region_query = f"and f.network_region='{network_region}'" query = __query.format( timezone=time_series.network.timezone_database, network_id=time_series.network.code, region_query=region_query, date_start=time_series.get_range().start, date_end=time_series.get_range().end, ) return dedent(query)
def test_schema_timeseries( ts: TimeSeries, start_expected: Union[datetime, date], end_expected: Union[datetime, date], interval_expected: str, length_expected: int, ) -> None: subject_daterange = ts.get_range() assert str( subject_daterange.start) == str(start_expected), "Start string matches" assert subject_daterange.start == start_expected, "Start matches" assert str( subject_daterange.end) == str(end_expected), "End string matches" assert subject_daterange.end == end_expected, "End matches" assert subject_daterange.trunc == interval_expected, "Interval matches"
def power_facility_query( time_series: TimeSeries, facility_codes: List[str], ) -> str: __query = """ select t.trading_interval at time zone '{timezone}', coalesce(avg(t.facility_power), 0), t.facility_code from ( select time_bucket_gapfill('{trunc}', fs.trading_interval) AS trading_interval, coalesce( avg(fs.generated), 0 ) as facility_power, fs.facility_code from facility_scada fs join facility f on fs.facility_code = f.code where fs.trading_interval <= '{date_max}' and fs.trading_interval > '{date_min}' and fs.facility_code in ({facility_codes_parsed}) group by 1, 3 ) as t group by 1, 3 order by 1 desc """ date_range = time_series.get_range() query = __query.format( facility_codes_parsed=duid_in_case(facility_codes), trunc=time_series.interval.interval_sql, period=time_series.period.period_sql, timezone=time_series.network.timezone_database, date_max=date_range.end, date_min=date_range.start, ) return query
def network_region_price_query(time_series: TimeSeries) -> str: __query = """ select time_bucket('{trunc}', bs.trading_interval) as trading_interval, bs.network_id, bs.network_region, coalesce(avg(bs.price), avg(bs.price_dispatch)) as price from balancing_summary bs where bs.trading_interval >= '{date_min}' and bs.trading_interval <= '{date_max}' and bs.network_id = '{network_id}' {network_regions_query} group by 1, 2, 3; """ date_range = time_series.get_range() date_min: datetime = date_range.end - timedelta(days=1) network_regions_query = "" if time_series.network.regions: network_regions: str = ",".join( [f"'{i.code}'" for i in time_series.network.regions]) network_regions_query = f"and bs.network_region IN ({network_regions})" query = dedent( __query.format( network_id=time_series.network.code, trunc=time_series.interval.interval_human, date_max=date_range.end, date_min=date_min, network_regions_query=network_regions_query, )) return query
def emission_network_fueltech_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: """Query emission stats for each network and fueltech""" __query = """ select time_bucket_gapfill('{trunc}', fs.trading_interval) AS trading_interval, ft.code as fueltech_code, case when sum(fs.generated) > 0 then sum(fs.generated) / {intervals_per_hour} * max(f.emissions_factor_co2) else 0 end as emissions, coalesce(max(fs.generated), 0) as fueltech_power from facility_scada fs join facility f on fs.facility_code = f.code join fueltech ft on f.fueltech_id = ft.code where fs.is_forecast is False and f.fueltech_id is not null and {network_query} {network_region_query} fs.trading_interval <= '{date_max}' and fs.trading_interval >= '{date_min}' {fueltech_filter} group by 1, f.code, 2 """ network_region_query: str = "" fueltech_filter: str = "" timezone: str = time_series.network.timezone_database fueltechs_excluded = [ "exports", "imports", "interconnector", "solar_rooftop", "solar_utility", "wind" ] if network_region: network_region_query = f"f.network_region='{network_region}' and " network_query = f"f.network_id ='{time_series.network.code}' and" date_max = time_series.get_range().end date_min = time_series.get_range().start fueltechs_exclude = ", ".join("'{}'".format(i) for i in fueltechs_excluded) query = dedent( __query.format( network_query=network_query, trunc=time_series.interval.interval_sql, network_region_query=network_region_query, timezone=timezone, date_max=date_max, date_min=date_min, fueltech_filter=fueltech_filter, fueltechs_exclude=fueltechs_exclude, intervals_per_hour=time_series.network.intervals_per_hour, )) return query
def energy_network_interconnector_emissions_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: """ Get emissions for a network or network + region based on a year """ if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select date_trunc('{trunc}', t.trading_interval at time zone '{timezone}') as trading_interval, sum(t.imports_energy) / 1000, sum(t.exports_energy) / 1000, abs(sum(t.emissions_imports)), abs(sum(t.emissions_exports)), sum(t.market_value_imports) as market_value_imports, sum(t.market_value_exports) as market_value_exports from ( select time_bucket_gapfill('5 min', t.trading_interval) as trading_interval, t.network_id, t.network_region, coalesce(t.energy_imports, 0) as imports_energy, coalesce(t.energy_exports, 0) as exports_energy, coalesce(t.emissions_imports, 0) as emissions_imports, coalesce(t.emissions_exports, 0) as emissions_exports, coalesce(t.market_value_imports, 0) as market_value_imports, coalesce(t.market_value_exports, 0) as market_value_exports from at_network_flows t where t.trading_interval <= '{date_max}' and t.trading_interval >= '{date_min}' and t.network_id = '{network_id}' and {network_region_query} ) as t group by 1 order by 1 desc """ timezone = time_series.network.timezone_database network_region_query = "" date_range = time_series.get_range() if network_region: network_region_query = f""" t.network_region = '{network_region}' """ query = dedent( __query.format( timezone=timezone, trunc=date_range.interval.trunc, network_id=time_series.network.code, date_min=date_range.start, date_max=date_range.end, network_region_query=network_region_query, )) return query
def energy_network_fueltech_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: """ Get Energy for a network or network + region based on a year """ if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) if time_series.interval.interval > 1440: __query = """ select date_trunc('{trunc}', t.trading_day) as trading_month, t.fueltech_id, coalesce(sum(t.fueltech_energy) / 1000 , 0) as fueltech_energy, coalesce(sum(t.fueltech_market_value), 0) as fueltech_market_value, coalesce(sum(t.fueltech_emissions), 0) as fueltech_emissions from (select time_bucket_gapfill('1 day', t.ti_day_aest) as trading_day, t.fueltech_id, sum(t.energy) as fueltech_energy, sum(t.market_value) as fueltech_market_value, sum(t.emissions) as fueltech_emissions from mv_facility_all t where t.ti_day_aest <= '{date_max}' and t.ti_day_aest >= '{date_min}' and t.fueltech_id not in ('imports', 'exports') and {network_query} {network_region_query} 1=1 group by 1, 2) as t group by 1, 2 order by 1 desc; """ else: __query = """ select t.ti_{trunc_name} as trading_day, t.fueltech_id, coalesce(sum(t.energy) / 1000, 0) as fueltech_energy, coalesce(sum(t.market_value), 0) as fueltech_market_value, coalesce(sum(t.emissions), 0) as fueltech_emissions from mv_facility_all t where t.trading_interval <= '{date_max}' and t.trading_interval >= '{date_min}' and t.fueltech_id not in ('imports', 'exports') and {network_query} {network_region_query} 1=1 group by 1, 2 order by trading_day desc; """ network_region_query = "" date_range = time_series.get_range() if network_region: network_region_query = f"t.network_region='{network_region}' and" networks_list = networks_to_in(networks_query) network_query = "t.network_id IN ({}) and ".format(networks_list) trunc_name = "{}_{}".format( time_series.interval.trunc, time_series.network.timezone_database ).lower() query = dedent( __query.format( trunc=date_range.interval.trunc, trunc_name=trunc_name, date_min=date_range.start, date_max=date_range.end, network_query=network_query, network_region_query=network_region_query, ) ) return query
def power_network_rooftop_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, forecast: bool = False, ) -> str: """Query power stats""" if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select time_bucket_gapfill('30 minutes', fs.trading_interval) AS trading_interval, ft.code as fueltech_code, {agg_func}(fs.generated) as facility_power from facility_scada fs join facility f on fs.facility_code = f.code join fueltech ft on f.fueltech_id = ft.code where {forecast_query} f.fueltech_id = 'solar_rooftop' and {network_query} {network_region_query} fs.trading_interval <= '{date_max}' and fs.trading_interval >= '{date_min}' group by 1, 2 order by 1 desc """ network_region_query: str = "" wem_apvi_case: str = "" agg_func = "sum" timezone: str = time_series.network.timezone_database forecast_query = f"fs.is_forecast is {forecast} and" if network_region: network_region_query = f"f.network_region='{network_region}' and " if NetworkWEM in networks_query: # silly single case we'll refactor out # APVI network is used to provide rooftop for WEM so we require it # in country-wide totals wem_apvi_case = "or (f.network_id='APVI' and f.network_region='WEM')" agg_func = "max" network_query = "(f.network_id IN ({}) {}) and ".format( networks_to_in(networks_query), wem_apvi_case) date_max = time_series.get_range().end date_min = time_series.get_range().start if forecast: # @TODO work out what in get_range is mashing this date_min = time_series.start + timedelta(minutes=30) date_max = date_min + timedelta(hours=3) query = dedent( __query.format( network_query=network_query, network_region_query=network_region_query, timezone=timezone, date_max=date_max, date_min=date_min, forecast_query=forecast_query, agg_func=agg_func, )) return query
def power_network_fueltech_query( time_series: TimeSeries, network_region: Optional[str] = None, networks_query: Optional[List[NetworkSchema]] = None, ) -> str: """Query power stats""" if not networks_query: networks_query = [time_series.network] if time_series.network not in networks_query: networks_query.append(time_series.network) __query = """ select time_bucket_gapfill('{trunc}', fs.trading_interval) AS trading_interval, ft.code as fueltech_code, coalesce(sum(fs.generated), 0) as fueltech_power from facility_scada fs join facility f on fs.facility_code = f.code join fueltech ft on f.fueltech_id = ft.code where fs.is_forecast is False and f.fueltech_id is not null and f.fueltech_id not in ({fueltechs_exclude}) and {network_query} {network_region_query} fs.trading_interval <= '{date_max}' and fs.trading_interval >= '{date_min}' {fueltech_filter} group by 1, 2 """ network_region_query: str = "" fueltech_filter: str = "" wem_apvi_case: str = "" timezone: str = time_series.network.timezone_database fueltechs_excluded = ["exports", "imports", "interconnector"] if NetworkNEM in networks_query: fueltechs_excluded.append("solar_rooftop") if network_region: network_region_query = f"f.network_region='{network_region}' and " if NetworkWEM in networks_query: # silly single case we'll refactor out # APVI network is used to provide rooftop for WEM so we require it # in country-wide totals wem_apvi_case = "or (f.network_id='APVI' and f.network_region='WEM')" network_query = "(f.network_id IN ({}) {}) and ".format( networks_to_in(networks_query), wem_apvi_case) date_max = time_series.get_range().end date_min = time_series.get_range().start fueltechs_exclude = ", ".join("'{}'".format(i) for i in fueltechs_excluded) query = dedent( __query.format( network_query=network_query, trunc=time_series.interval.interval_sql, network_region_query=network_region_query, timezone=timezone, date_max=date_max, date_min=date_min, fueltech_filter=fueltech_filter, wem_apvi_case=wem_apvi_case, fueltechs_exclude=fueltechs_exclude, )) return query
def weather_observation_query(time_series: TimeSeries, station_codes: List[str]) -> str: if time_series.interval.interval >= 1440: # @TODO replace with mv __query = """ select date_trunc('{trunc}', t.observation_time at time zone '{tz}') as observation_month, t.station_id, avg(t.temp_avg), min(t.temp_min), max(t.temp_max) from ( select time_bucket_gapfill('1 day', observation_time) as observation_time, fs.station_id, case when avg(fs.temp_air) is not null then avg(fs.temp_air) when max(fs.temp_max) is not null and max(fs.temp_min) is not null then ((max(fs.temp_max) + min(fs.temp_min)) / 2) else NULL end as temp_avg, case when min(fs.temp_min) is not null then min(fs.temp_min) else min(fs.temp_air) end as temp_min, case when max(fs.temp_max) is not null then max(fs.temp_max) else max(fs.temp_air) end as temp_max from bom_observation fs where fs.station_id in ({station_codes}) and fs.observation_time <= '{date_end}' and fs.observation_time >= '{date_start}' group by 1, 2 ) as t group by 1, 2; """.format( trunc=time_series.interval.trunc, tz=time_series.network.timezone_database, station_codes=",".join(["'{}'".format(i) for i in station_codes]), date_start=time_series.get_range().start, date_end=time_series.get_range().end, ) else: __query = """ select fs.observation_time at time zone '{tz}' as ot, fs.station_id as station_id, avg(fs.temp_air) as temp_air, case when min(fs.temp_min) is not null then min(fs.temp_min) else min(fs.temp_air) end as temp_min, case when max(fs.temp_max) is not null then max(fs.temp_max) else max(fs.temp_air) end as temp_max from bom_observation fs where fs.station_id in ({station_codes}) and fs.observation_time <= '{date_end}' and fs.observation_time >= '{date_start}' group by 1, 2; """.format( tz=time_series.network.timezone_database, station_codes=",".join(["'{}'".format(i) for i in station_codes]), date_start=time_series.get_range().start, date_end=time_series.get_range().end, ) return dedent(__query)