Schinckel.net

Having five-minute interval generation and consumption data from my PV system means that, in theory at least, I should be able to do some predictive modelling of what adding a battery to the system will do to my import and export levels, and more importantly, determine how much money having a battery of a specific capacity and efficiency would save me.

There are several approaches that could be used to model a battery. They all boil down to the same main idea:

• The battery has a fixed capacity
• The battery has a current level
• If generating more power than consuming, this excess could be fed into the battery (assuming the battery is not full)
• If the battery is full, any excess generation is exported
• If we are consuming more power than generating, and we have battery level > 0, then we draw power from the battery
• Any power we are drawing from the battery is divided by the round-trip efficiency of the battery
• Any excess consumption that cannot be supplied by the battery results in imports

There are some assumptions that this model makes.

• There is no current (as in Amperes, not “now”) limit to either exporting or importing
• If we are both importing and exporting, the battery can handle both of these. In practice, most batteries take some time to switch between charging and discharging
• The usable capacity is the same as the battery capacity

In all of these cases, the limitations we are not addressing would more than likely result in a poorer outcome than the model will predict.

So, on to the technical details of creating the model. In the first instance, I tried using a recursive view (using in this case the 13.5kWh/90% efficient Tesla Powerwall):

CREATE OR REPLACE RECURSIVE VIEW battery_status(
  timestamp,
  capacity,
  efficiency,
  level,
  import,
  export
) AS

SELECT '2019-03-07'::TIMESTAMP,
       13500 AS capacity,
       0.90 AS efficiency,
       0::NUMERIC AS level,
       0::NUMERIC AS import,
       0::NUMERIC AS export

 UNION ALL

SELECT battery.timestamp + INTERVAL '5 minutes',
       battery.capacity,
       battery.efficiency,
       GREATEST(
         0,
         battery.level + delta.amount_to_battery - delta.amount_from_battery
       )::NUMERIC(12, 3) AS level,
       (summary.import - delta.amount_from_battery)::NUMERIC(8, 3) AS import,
       (summary.export - delta.amount_to_battery)::NUMERIC(8, 3) AS export
  FROM battery_status battery
  LEFT OUTER JOIN summary ON (summary.timestamp = battery.timestamp + INTERVAL '5 minutes')
 INNER JOIN LATERAL (
   SELECT LEAST(summary.import, battery.level / battery.efficiency) AS amount_from_battery,
          LEAST(summary.export, battery.capacity - battery.level) AS amount_to_battery
 ) delta ON (true)

Whilst this sort-of worked, it was unsatisfactory in a couple of ways. Firstly, it took ages to run. Way longer than I expected, even when I used materialised views, and changed the interval to hourly instead of 5 minutely.

Additionally, because I have some gaps in my data (for instance, when my power was disconnected, or the inverter was off because I was cleaning the panels, or whatever), the view stopped recursing at this point, so I was never actually able to get a result that went for more than a couple of months. Even generating missing values seemed to be insufficient, so at some point I gave up on this.

I even tried the same approach on a daily summary - this was never going to give me anything close to an accurate result, but at that point I was stretching thin, so tried a bunch of things.

It seemed unusual that it was taking a long as it did (dozens of seconds, and that was only to build a few months of data). It should only need to pass over the summary data once, and store…that gave me an idea. I could write a fairly simple python generator function to perform the modeling.

def battery_status(capacity, efficiency):
    result = plpy.execute('SELECT * FROM summary')
    energy = 0

    for row in result:
        export_amount = row['export']
        if export_amount and energy < capacity:
            energy += export_amount
            if energy > capacity:
                export_amount = energy - capacity
                energy = capacity
            else:
                export_amount = 0
        import_amount = row['import']
        if import_amount and energy > 0:
            energy -= import_amount / efficiency
            if energy < 0:
                import_amount = -energy * efficiency
                energy = 0
            else:
                import_amount = 0
        yield (row['timestamp'], energy, import_amount, export_amount)

Following it through, we can see some benefits to this approach. We don’t need to pass the capacity and efficiency (or the current energy level in the battery) through to each iteration: because this is a generator, the state remains in the function, and those variables are just re-used over and over again.

The only thing that might be unusual there is the plpy.execute('SELECT * FROM summary'): this hints that this code is actually going inside a plpythonu function and living inside the database. We can use a composite type to make the return values easier to deal with:

CREATE TYPE battery_status AS (
    timestamp TIMESTAMP,
    stored NUMERIC,
    import NUMERIC,
    export NUMERIC
);

CREATE FUNCTION battery_status(capacity NUMERIC, efficiency NUMERIC)
RETURNS SETOF battery_status AS $$

result = plpy.execute('SELECT * FROM summary')
energy = 0

for row in result:
    export_amount = row['export']
    if export_amount and energy < capacity:
        energy += export_amount
        if energy > capacity:
            export_amount = energy - capacity
            energy = capacity
        else:
            export_amount = 0
    import_amount = row['import']
    if import_amount and energy > 0:
        energy -= import_amount / efficiency
        if energy < 0:
            import_amount = -energy * efficiency
            energy = 0
        else:
            import_amount = 0
    yield (row['timestamp'], energy, import_amount, export_amount)

$$ LANGUAGE plpythonu STRICT IMMUTABLE;

Again, I’ll reiterate that in this context, we apply both charging and discharging in the same period (which could happen), but assumes that there is zero turnaround time required between charge and discharge, and that there is no limit to the charge and discharge rate. We could probably improve the model to take those two into account fairly easily though.

To revisit what the other data stores look like, let’s have a look at my summary view:

CREATE OR REPLACE VIEW summary AS

SELECT "timestamp",
       COALESCE(generation.energy, 0::double precision) AS generation,
       COALESCE(import.energy, 0) AS import,
       COALESCE(export.energy, 0) AS export,
       import.energy::double precision
           + generation.energy
           - export.energy::double precision AS consumption
  FROM generation
  JOIN import USING ("timestamp")
  JOIN export USING ("timestamp")

My generation table just contains the generation for each interval: the import and export are both views that use the cumulative import/export totals and calculate the value by subtracting the previous row:

CREATE OR REPLACE VIEW import AS

SELECT import_cumulative."timestamp",
       import_cumulative.energy
           - lag(import_cumulative.energy, 1) OVER (ORDER BY import_cumulative."timestamp")
           AS energy
  FROM import_cumulative
 ORDER BY import_cumulative."timestamp";

We can create a materialised view of the battery status per-day, since that’s all we will need for the purposes of cost calculations:

CREATE MATERIALIZED VIEW daily_summary_battery AS

SELECT battery_status."timestamp"::date AS "timestamp",
       sum(battery_status.import) AS import,
       sum(battery_status.export) AS export
  FROM battery_status(13500::numeric, 0.9)
       battery_status("timestamp", stored, import, export)
 GROUP BY (battery_status."timestamp"::date)
 ORDER BY (battery_status."timestamp"::date);

So, lets have a look at what results we get.

Refreshing the materialised view takes me around 9 seconds (this is quite stable), and I currently have about 106000 records (370 days of data, 288 records per day if we got them all). This is nice: it’s not immediate, but not going to be a deal-breaker if I need to run a bunch of different models of different battery capacity or efficiencies.

I can use the same cost functions from my actual cost calculator to look at what this battery model will do to my costs:

CREATE VIEW daily_battery_cost AS
SELECT daily_summary_battery."timestamp" AS date,
       daily_summary_battery.import,
       daily_summary_battery.export,
       cost(retailer.*,
            daily_summary_battery.import::integer,
            daily_summary_battery.export::integer) AS cost,
       billing_periods.period
  FROM daily_summary_battery
  JOIN billing_periods ON daily_summary_battery."timestamp" <@ billing_periods.period
  JOIN retailer ON retailer.name = billing_periods.retailer
 ORDER BY daily_summary_battery."timestamp";

And for comparisons in my billing periods:

CREATE VIEW billing_period_battery_costs AS
SELECT daily_battery_cost.period,
       sum(daily_battery_cost.cost) AS cost
  FROM daily_battery_cost
 GROUP BY daily_battery_cost.period
 ORDER BY daily_battery_cost.period;

CREATE VIEW billing_period_battery_savings AS
SELECT costs.period,
       costs.cost - battery.cost AS savings
  FROM billing_period_costs costs
  JOIN billing_period_battery_costs battery USING (period)
 ORDER BY costs.period;

solar=# SELECT * FROM billing_period_battery_savings ;
         period          |            savings
-------------------------+-------------------------------
 [2019-03-07,2019-06-01) | 101.6372820000000000000000000
 [2019-06-01,2019-06-24) |      15.592432500000000000000
 [2019-06-24,2019-07-24) |  20.4731725000000000000000000
 [2019-07-24,2019-08-24) |      17.898683500000000000000
 [2019-08-24,2019-09-24) |  18.5995855000000000000000000
 [2019-09-24,2019-10-24) |  35.1526490000000000000000000
 [2019-10-24,2019-11-24) |  38.4160700000000000000000000
 [2019-11-24,2019-12-24) |  37.7519350000000000000000000
 [2019-12-24,2020-01-24) |  39.2153900000000000000000000
 [2020-01-24,2020-02-24) |  39.5369135000000000000000000
 [2020-02-24,2020-03-24) |  21.2207440000000000000000000
(11 rows)

And for a per-day saving: it works out to around $1.05 per day.

Which is ~$380 per year.

A Tesla Powerwall 2 battery would cost me (excluding installation, but including the SA battery rebate), in excess of $5000. That’s still almost a 15 year payback time.

What’s quite amusing is that I distinctly remember calculating what I thought a battery would save me shortly after getting my PV system installed. I have such a strong recollection of it that I can remember exactly where I was: I was out running at lunch time, and was partway up the hill at the back of Flinders Uni.

And my in-head calculations were that it would save me $1 per day.

I’m actually more interested in a DC coupled battery system, partly because of the reduction in round-trip efficiency losses, but more so because I should be able to stick more panels in that would just feed the battery. I’m especially interested in the Fronius Gen24 inverters and a compatible battery, however there aren’t clear details on what that would cost. The benefit of this is that in SA there is currently a 10kW inverter limit; this includes the inverter in an AC coupled battery like the Tesla Powerwall.

These are likely to come midway through this year. I’ll be interested to see the pricing.

I think I’ll need to do a bit more on my calculations to be able to model this: my panel_factor calculations have a 5kW limit - this goes away, and instead the limit applies after taking the battery charging into account. But this can wait for another day.