def get_generated_query( date_min: datetime, date_max: datetime, network: NetworkSchema, network_region: Optional[str] = None, fueltech_id: Optional[str] = None, facility_codes: Optional[List[str]] = None, ) -> str: # @TODO support refresh energies for a single duid or station __sql = """ select fs.trading_interval at time zone '{timezone}' as trading_interval, fs.facility_code, fs.network_id, f.fueltech_id, generated from facility_scada fs left join facility f on fs.facility_code = f.code where f.network_id='{network_id}' {network_region_query} and fs.trading_interval >= '{date_min}' and fs.trading_interval <= '{date_max}' and fs.is_forecast is False {fueltech_match} {facility_match} and fs.generated is not null order by fs.trading_interval asc, 2 """ fueltech_match = "" facility_match = "" network_region_query = "" if fueltech_id: fueltech_match = f"and f.fueltech_id = '{fueltech_id}'" if facility_codes: facility_match = f"and f.code in ({duid_in_case(facility_codes)})" if network_region: network_region_query = f"and f.network_region='{network_region}'" query = __sql.format( timezone=network.timezone_database, network_id=network.code, network_region=network_region, network_region_query=network_region_query, date_min=date_min.replace(tzinfo=network.get_fixed_offset()), date_max=(date_max + timedelta(minutes=5)).replace( tzinfo=network.get_fixed_offset()), fueltech_match=fueltech_match, facility_match=facility_match, ) return dedent(query)
def run_energy_update_today(network: NetworkSchema = NetworkNEM, days: int = 1) -> None: """Run energy sum update for yesterday. This task is scheduled in scheduler/db This is NEM only atm""" # This is Sydney time as the data is published in local time tz = pytz.timezone("Australia/Sydney") # today_midnight in NEM time today_midnight = datetime.now(tz).replace( tzinfo=network.get_fixed_offset(), microsecond=0, hour=0, minute=0, second=0) date_max = today_midnight + timedelta(days=1) date_min = today_midnight regions = [i.code for i in get_network_regions(network)] for region in regions: run_energy_calc(region, date_min, date_max, network=network)
def shape_energy_dataframe( gen_series: List[Dict], network: NetworkSchema = NetworkNEM) -> pd.DataFrame: """ Shapes a list of dicts into a dataframe for energy_sum""" df = pd.DataFrame( gen_series, columns=[ "trading_interval", "facility_code", "network_id", "fueltech_id", "generated", ], ) # Clean up types df.trading_interval = pd.to_datetime(df.trading_interval) df.generated = pd.to_numeric(df.generated) # timezone from network df.trading_interval = df.apply(lambda x: pd.Timestamp( x.trading_interval, tz=network.get_fixed_offset()), axis=1) return df
def energy_sum(df: pd.DataFrame, network: NetworkSchema, power_column: str = "generated") -> pd.DataFrame: """Takes the energy sum for a series of raw duid intervals and returns a fresh dataframe to be imported""" # Index by datetime df = df.set_index(["trading_interval", "network_id", "facility_code"]) # Multigroup by datetime and facility code df = _energy_aggregate(df, power_column=power_column) # Reset back to a simple frame df = df.reset_index() # Shift back 5 minutes to even up into 30 minute bkocks if network.reading_shift: df.trading_interval = df.trading_interval - pd.Timedelta( minutes=network.reading_shift) # Add back the timezone for NEM # We use a fixed offset so need to loop df.trading_interval = df.apply(lambda x: pd.Timestamp( x.trading_interval, tz=network.get_fixed_offset()), axis=1) # filter out empties df = df[pd.isnull(df.eoi_quantity) == False] return df
def networks(self) -> List[NetworkSchema]: """Return networks.""" resp = self._get("networks") resp_objects = [NetworkSchema(**i) for i in resp] return resp_objects
def networks( session: Session = Depends(get_database_session), ) -> List[NetworkSchema]: networks = session.query(Network).all() if not networks: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND) response = [NetworkSchema.parse_obj(i) for i in networks] return response
def _get_year_range( year: int, network: NetworkSchema = NetworkNEM) -> Tuple[datetime, datetime]: """Get a date range for a year with end exclusive""" tz = network.get_fixed_offset() date_min = datetime(year, 1, 1, 0, 0, 0, 0, tzinfo=tz) date_max = datetime(year + 1, 1, 1, 0, 0, 0, 0, tzinfo=tz) if year == DATE_CURRENT_YEAR: date_max = datetime.now().replace(hour=0, minute=0, second=0, tzinfo=tz) return date_min, date_max
def daily_fueltech_summary_query(day: datetime = DATE_YESTERDAY, network: NetworkSchema = NetworkNEM) -> str: EXCLUDE_FUELTECHS = [ "imports", "exports", "interconnector", "battery_discharging" ] day_date = day.replace(tzinfo=network.get_fixed_offset()) date_min = date_trunc(day_date, truncate_to="day") date_max = date_min + timedelta(days=1) query = __daily_fueltech_query.format( date_min=date_min, date_max=date_max, tz=network.timezone_database, network_id=network.code, fueltechs_excluded=duid_in_case(EXCLUDE_FUELTECHS), ) return dedent(query)
def aggregates_network_demand_query(date_max: datetime, date_min: datetime, network: NetworkSchema) -> str: """This query updates the aggregate demand table with market_value and energy""" __query = """ insert into at_network_demand select date_trunc('day', fs.trading_interval at time zone n.timezone_database {network_interval_offset}) as trading_day, fs.network_id, fs.network_region, sum(fs.energy) as demand_energy, sum(fs.market_value) as demand_market_value from ( select time_bucket_gapfill('5 minutes', bs.trading_interval) as trading_interval, bs.network_id, bs.network_region, (sum(coalesce(bs.demand_total, bs.demand)) / {intervals_per_hour}) as energy, (sum(coalesce(bs.demand_total, bs.demand)) / {intervals_per_hour}) * max(bs.price_dispatch) * 1000 as market_value from balancing_summary bs where bs.network_id = '{network_id}' and bs.trading_interval >= '{date_min}' and bs.trading_interval <= '{date_max}' group by 1, 2, 3 ) as fs left join network n on fs.network_id = n.code group by 1, 2, 3 on conflict (trading_day, network_id, network_region) DO UPDATE set demand_energy = EXCLUDED.demand_energy, demand_market_value = EXCLUDED.demand_market_value; """ date_min_offset = date_min.replace(tzinfo=network.get_fixed_offset()) date_max_offset = date_max.replace(hour=0, minute=0, second=0, microsecond=0) if date_max_offset <= date_min_offset: raise Exception( "aggregates_network_demand_query: date_max ({}) is before date_min ({})" .format(date_max_offset, date_min)) network_interval_offset = "" if network.interval_shift: network_interval_offset = f" - interval '{network.interval_shift} minutes'" query = __query.format( network_interval_offset=network_interval_offset, date_min=date_min_offset, date_max=date_max_offset, network_id=network.code, intervals_per_hour=network.intervals_per_hour * 1000, ) return dedent(query)
def aggregates_facility_daily_query(date_max: datetime, date_min: datetime, network: NetworkSchema) -> str: """This is the query to update the at_facility_daily aggregate""" __query = """ insert into at_facility_daily select date_trunc('day', fs.trading_interval at time zone n.timezone_database) as trading_day, f.network_id, f.code as facility_code, f.fueltech_id, sum(fs.energy) as energy, sum(fs.market_value) as market_value, sum(fs.emissions) as emissions from ( select time_bucket_gapfill('{network_interval_size} minutes', fs.trading_interval) as trading_interval, fs.facility_code as code, case when sum(fs.eoi_quantity) > 0 then coalesce(sum(fs.eoi_quantity), 0) else 0 end as energy, case when sum(fs.eoi_quantity) > 0 then coalesce(sum(fs.eoi_quantity), 0) * coalesce(max(bs.price_dispatch), max(bs.price), 0) else 0 end as market_value, case when sum(fs.eoi_quantity) > 0 then coalesce(sum(fs.eoi_quantity), 0) * coalesce(max(f.emissions_factor_co2), 0) else 0 end as emissions from facility_scada fs left join facility f on fs.facility_code = f.code left join network n on f.network_id = n.code left join balancing_summary bs on bs.trading_interval {trading_offset} = fs.trading_interval and bs.network_id = n.network_price and bs.network_region = f.network_region and f.network_id = '{network_id}' where fs.is_forecast is False and fs.network_id = '{network_id}' and fs.trading_interval >= '{date_min}' and fs.trading_interval < '{date_max}' group by 1, 2 ) as fs left join facility f on fs.code = f.code left join network n on f.network_id = n.code where f.fueltech_id is not null group by 1, f.network_id, f.code, f.fueltech_id on conflict (trading_day, network_id, facility_code) DO UPDATE set energy = EXCLUDED.energy, market_value = EXCLUDED.market_value, emissions = EXCLUDED.emissions; """ trading_offset = "" if network == NetworkNEM: trading_offset = "- INTERVAL '5 minutes'" date_min_offset = date_min.replace(tzinfo=network.get_fixed_offset()) date_max_offset = (date_max + timedelta(days=1)).replace( tzinfo=network.get_fixed_offset()) if date_max_offset <= date_min_offset: raise Exception( "aggregates_facility_daily_query: date_max ({}) is before date_min ({})" .format(date_max_offset, date_min)) query = __query.format( date_min=date_min_offset, date_max=date_max_offset, network_id=network.code, trading_offset=trading_offset, network_interval_size=network.interval_size, ) return dedent(query)
def datetime_add_network_timezone(dt: datetime, network: NetworkSchema) -> datetime: """ Returns a datetime in network timezone """ return dt.astimezone(network.get_fixed_offset())
def export_network_intervals_for_week( week_start: datetime, week_end: datetime, network: NetworkSchema, network_region: NetworkRegion, ) -> int | None: """ """ week_number = get_week_number_from_datetime(week_start) logging.info( "Exporting historic intervals for network {} and region {} and year {} and week {} ({} => {})" .format(network.code, network_region.code, week_start.year, week_number, week_start, week_end)) time_series = TimeSeries( start=week_start, end=week_end + timedelta(days=1), network=network, interval=network.get_interval(), period=human_to_period("7d"), ) stat_set = power_week( time_series=time_series, networks_query=network.get_networks_query(), network_region_code=network_region.code, ) if not stat_set: return None # emissions for network emission_intervals = emissions_for_network_interval( time_series=time_series, network_region_code=network_region.code) stat_set.append_set(emission_intervals) # demand and pricing demand_energy_and_value = demand_network_region_daily( time_series=time_series, network_region_code=network_region.code, networks=network.get_networks_query()) stat_set.append_set(demand_energy_and_value) if network == NetworkNEM: interconnector_flows = energy_interconnector_flows_and_emissions( time_series=time_series, networks_query=network.get_networks_query(), network_region_code=network_region.code, ) stat_set.append_set(interconnector_flows) bom_station = get_network_region_weather_station(network_region.code) if bom_station: try: weather_stats = weather_daily( time_series=time_series, station_code=bom_station, network_region=network_region.code, ) stat_set.append_set(weather_stats) except Exception: pass save_path = ( f"v3/stats/historic/weekly/{network.code}/{network_region.code}/year/{week_start.year}/week/{week_number}.json" ) written_bytes = write_output(save_path, stat_set) return written_bytes
def price_network_region( network: NetworkSchema, network_region_code: str, interval: TimeInterval, period: TimePeriod, scada_range: ScadaDateRange, year: Optional[int] = None, ) -> str: timezone = network.get_timezone(postgres_format=True) if not timezone: timezone = "UTC" __query = """ SET SESSION TIME ZONE '{timezone}'; select time_bucket_gapfill('{trunc}', bs.trading_interval) AS trading_interval, bs.network_region, coalesce(avg(bs.price), 0) as price from balancing_summary bs where bs.trading_interval >= {date_min_query} and bs.trading_interval <= {scada_max} {network_query} {network_region_query} group by 1, 2 order by 1 desc """ network_query = "" network_region_query = "" if network: network_query = f"and bs.network_id = '{network.code}' " if network_region_code: network_region_query = f"and bs.network_region='{network_region_code}' " date_min_query = "" if period: date_min_query = "{scada_max}::timestamp - interval '{period}'::interval".format( scada_max=scada_range.get_end_sql(), period=period.period_sql) if year: date_min_query = "'{year}-01-01'::date ".format(year=year) if not period and not year: raise HTTPException( status_code=status.HTTP_404_NOT_FOUND, detail="Require one of period or year", ) query = __query.format( trunc=interval.interval_sql, timezone=timezone, network_query=network_query, network_region_query=network_region_query, scada_max=scada_range.get_end_sql(), date_min_query=date_min_query, ) return query
def energy_network_fueltech_year( network: NetworkSchema, interval: TimeInterval, year: int, network_region: str = None, scada_range: ScadaDateRange = None, ) -> str: """ Get Energy for a network or network + region based on a year """ timezone = network.get_timezone(postgres_format=True) if not timezone: timezone = "UTC" year_max = "'{}-12-31'".format(year) if year == datetime.now().year: year_max = scada_range.get_end_sql(as_date=False) __query = """ SET SESSION TIME ZONE '{timezone}'; select t.trading_interval, sum(t.facility_energy), t.fueltech_code from ( select time_bucket_gapfill('{trunc}', trading_interval) AS trading_interval, energy_sum(fs.generated, '{trunc}') * interval_size('1 day', count(fs.generated)) / 1000 as facility_energy, f.code, ft.code as fueltech_code from facility_scada fs join facility f on fs.facility_code = f.code join fueltech ft on f.fueltech_id = ft.code where fs.trading_interval >= '{year}-01-01' and fs.trading_interval <= {year_max} and fs.network_id = '{network_code}' and f.fueltech_id is not null {network_region_query} group by 1, 3, 4 ) as t group by 1, 3 order by 1 desc; """ network_region_query = "" if network_region: network_region_query = f"and f.network_region='{network_region}'" query = __query.format( network_code=network.code, trunc=interval.interval_sql, year=year, year_max=year_max, scale=network.intervals_per_hour, network_region_query=network_region_query, timezone=timezone, ) return query
def power_facility_query( facility_codes: List[str], network: NetworkSchema, period: TimePeriod, interval: Optional[TimeInterval] = None, date_range: Optional[ScadaDateRange] = None, ) -> str: timezone = network.get_timezone(postgres_format=True) if not date_range: date_range = get_scada_range(network=network, facilities=facility_codes) timezone = network.timezone_database __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 """ if not interval: interval = network.get_interval() if not date_range: raise Exception("Require a date range for query") if not interval: raise Exception("Require an interval") date_max = date_range.get_end() date_min = date_range.get_start() if period: date_min = date_range.get_end() - timedelta(minutes=period.period) query = __query.format( facility_codes_parsed=duid_in_case(facility_codes), trunc=interval.interval_sql, period=period.period_sql, timezone=timezone, date_max=date_max, date_min=date_min, ) return query
def energy_facility_query( facility_codes: List[str], network: NetworkSchema, period: TimePeriod, interval: Optional[TimeInterval] = None, ) -> str: """ Get Energy for a list of facility codes """ __query = """ select date_trunc('{trunc}', t.trading_interval at time zone '{timezone}') as trading_day, t.code, sum(t.energy) / 1000 as fueltech_energy, sum(t.market_value) as fueltech_market_value, sum(t.emissions) as fueltech_emissions from mv_facility_all t where t.trading_interval <= '{date_max}' and t.trading_interval >= '{date_min}' and t.code in ({facility_codes_parsed}) group by 1, 2 order by trading_day desc; """ timezone = network.timezone_database offset = network.get_timezone(postgres_format=True) date_range: ScadaDateRange = get_scada_range(network=network, facilities=facility_codes) if not interval: interval = network.get_interval() if not date_range: raise Exception("Require a date range for query") if not period: raise Exception("Require a period") if not interval: interval = network.get_interval() if not interval: raise Exception("Require an interval") trunc = interval.trunc date_max = date_range.get_end() date_min = date_range.get_start() if period.period_human == "1M": date_min = date_range.get_end() - timedelta(minutes=period.period) elif period.period_human == "1Y": # might have to do +offset times year = datetime.now().year date_min = "{}-01-01 00:00:00{}".format(year, offset) elif period.period_human in ["7d", "5Y", "10Y"]: date_min = date_range.get_end() - timedelta(minutes=period.period) # elif period.period_human == "all": # else: # date_min = date_range.get_end() - timedelta(minutes=period.period) query = dedent( __query.format( facility_codes_parsed=duid_in_case(facility_codes), trunc=trunc, date_max=date_max, date_min=date_min, timezone=timezone, )) return query
def power_network_fueltech( network: NetworkSchema, interval: TimeInterval, period: TimePeriod, network_region: Optional[str] = None, scada_range: Optional[ScadaDateRange] = None, ) -> str: timezone = network.get_timezone(postgres_format=True) if not timezone: timezone = "UTC" __query = """ SET SESSION TIME ZONE '{timezone}'; select t.trading_interval, sum(t.facility_power), t.fueltech_code from ( select time_bucket_gapfill('{trunc}', trading_interval) AS trading_interval, coalesce( avg(fs.generated), 0 ) as facility_power, fs.facility_code, ft.code as fueltech_code from facility_scada fs join facility f on fs.facility_code = f.code join fueltech ft on f.fueltech_id = ft.code where fs.trading_interval <= {date_end} and fs.trading_interval >= {date_end}::timestamp - '{period}'::interval and fs.network_id = '{network_code}' and f.fueltech_id is not null {network_region_query} group by 1, 3, 4 ) as t group by 1, 3 order by 1 desc """ network_region_query = "" if network_region: network_region_query = f"and f.network_region='{network_region}'" date_end = "now()" if scada_range: date_end = scada_range.get_end_sql() query = __query.format( network_code=network.code, trunc=interval.interval_sql, period=period.period_sql, network_region_query=network_region_query, timezone=timezone, date_end=date_end, ) return query