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