UK electricity demand reduction calculator

EDIT: query updated for the v32 database schema introduced in 2023.2: the last_updated date-time has changed to last_updated_ts epoch timestamp.

Here in the UK, the grid operator has introduced a demand flexibility service to incentivise reduction of electricity usage at peak times to avoid having to fire up the coal plants.

My supplier, Octopus (sneaky referral link) has implemented this as Saving Sessions, where you get rewarded with points for reducing your energy use during one of these slots versus your normal usage at that time of day. Yesterday’s session, for example, was worth £3.37 / kWh not used versus baseline.

To see what the approximate potential saving is, I want to know what my usage over the announced time period has been on previous days. I have a power meter that counts flashes on my electricity meter and gives me an average power per minute reading, and a Riemann integration sensor set up from that (sensor.house_energy) that feeds my Energy dashboard.

As we don’t know in advance what the time period of a slot is going to be, I can’t pre-record the data, so I need to look it up from the HA database. I created the following:

  • a time-only input helper to record the start time
  • a time-only input helper to record the end time
  • an automation to force an update (see below)
  • an SQL sensor with the following query:
SELECT AVG(energy) AS average FROM
  (SELECT end-start AS energy, day FROM
    (SELECT SUBSTRING(datetime(last_updated_ts,'unixepoch'),1,10) AS day,
            SUBSTRING(time(last_updated_ts,'unixepoch'),1,5) AS ts,
            MIN(state) AS start,
            MAX(state) AS end FROM states
       WHERE entity_id = 'sensor.house_energy' AND
             ts >= (SELECT SUBSTRING(state,1,5) FROM states
                      WHERE entity_id = 'input_datetime.saving_session_start_time'
                      ORDER BY state_id DESC LIMIT 1) AND
             ts <  (SELECT SUBSTRING(state,1,5) FROM states
                      WHERE entity_id = 'input_datetime.saving_session_end_time'
                      ORDER BY state_id DESC LIMIT 1) AND NOT
             day = SUBSTRING(date(),1,10) AND NOT
             state = "unknown"
       GROUP BY day));

…set up like this:

There’s probably a shorter way to write that query, but it does the following:

  • third SELECT creates a list of start and end energy values per day for the timeslot within the scope of the database’s recording window excluding today;
  • second SELECT generates a table of differences between end and start (which is the energy used) for each day;
  • and the first SELECT averages those differences.

Note that the SQL sensor doesn’t accept templates in the query string, so to include the start and end times I pull the latest database value for both of the input_datetime entities, with the SUBSTRING statement getting just the hh:mm part.

The automation forces an update of the SQL sensor, waiting to allow the new time values to be written to the database.

- alias: Energy - update saving session average calculation
  id: 30d97745-69b7-4858-a00a-5103fcccf29c
  trigger:
    - platform: state
      entity_id:
        - input_datetime.saving_session_start_time
        - input_datetime.saving_session_end_time
  action:
    - delay: 5
    - service: homeassistant.update_entity
      data: {}
      target:
        entity_id: sensor.energy_averages

That lets me create this UI widget:

image

…allowing me to see what my average energy consumption over the previous timeslots has been. This won’t be exactly how your supplier calculates it, as they’ll likely use a different window; and they should exclude any prior Savings Session days from the results.

Note I’ve also created an “SS today” sensor that has the same SQL as above but without the NOT in the day selector, so I can see how much i used after the session.

Even with the automation, the sensor can take several seconds to update to the correct value when you change the input_datetimes as it relies on the values going into the database. I’ve included the secondary_info: last-changed on the card so I can see if the values are fresh.

1 Like

Results from today’s Saving Session — although I only have five days’ prior data at the moment, as that’s when I set up the Riemann sensor:

image

Now just need to wait and see what the official result looks like!

This is fantastic, thanks for sharing this!

One question (sorry, I’m useless at SQL!) but how should the SQL query look for the above? Mine looks like the below but I’m just getting an ‘unknown’ value, so I think I’ve done it wrong:

SELECT AVG(energy) AS average FROM
  (SELECT end-start AS energy, day FROM
    (SELECT SUBSTRING(datetime(last_updated,'localtime'),1,10) AS day,
            SUBSTRING(datetime(last_updated,'localtime'),12,5) AS ts,
            MIN(state) AS start,
            MAX(state) AS end FROM states
       WHERE entity_id = 'sensor.smart_meter_electricity_import_today' AND
             ts >= (SELECT SUBSTRING(state,1,5) FROM states
                      WHERE entity_id = 'input_datetime.saving_session_start_time'
                      ORDER BY state_id DESC LIMIT 1) AND
             ts <  (SELECT SUBSTRING(state,1,5) FROM states
                      WHERE entity_id = 'input_datetime.saving_session_end_time'
                      ORDER BY state_id DESC LIMIT 1) AND NOT
             day = SUBSTRING(date(),1,10) AND
             state = "unknown"
       GROUP BY day));

Thanks!

EDIT: query updated for the v32 database schema introduced in 2023.2: the last_updated date-time has changed to last_updated_ts epoch timestamp.

You’ve removed the wrong NOT: it’s the one before the day you want to take out. So:

SELECT AVG(energy) AS average FROM
  (SELECT end-start AS energy, day FROM
    (SELECT SUBSTRING(datetime(last_updated_ts,'unixepoch'),1,10) AS day,
            SUBSTRING(time(last_updated_ts,'unixepoch'),1,5) AS ts,
            MIN(state) AS start,
            MAX(state) AS end FROM states
       WHERE entity_id = 'sensor.house_energy' AND
             ts >= (SELECT SUBSTRING(state,1,5) FROM states
                      WHERE entity_id = 'input_datetime.saving_session_start_time'
                      ORDER BY state_id DESC LIMIT 1) AND
             ts <  (SELECT SUBSTRING(state,1,5) FROM states
                      WHERE entity_id = 'input_datetime.saving_session_end_time'
                      ORDER BY state_id DESC LIMIT 1) AND
             day = SUBSTRING(date(),1,10) AND NOT
             state = "unknown"
       GROUP BY day));

Note that if your input_datetimes are not set, or are in the future for today, you will still get unknown as there are no values returned from the SQL.

That’s great, thanks for this. All working now!

image

1 Like

NOTE: v32 database schema update in 2023.2 requires a query update. Prior posts edited in line.

You might find that your log gets filled up with warnings about the “today” sensor returning no results ahead of the slot time. Adding the bottom line of this (and the rest of it if you don’t have a logger: section) to your configuration.yaml will stop it moaning:

logger:
  default:info
  logs:
    homeassistant.components.sql: critical

…which you can set dynamically without needing to restart your current session:

image

1 Like

Possibly too late in the season but I finally got around to playing around with the API that ESO provides to grab DFS information - a nice simple one for a first HA integration and first attempt at writing something in Python.

It’s very rough and ready, not even sorted with HACS yet, but can be found here.

As well as the schedule information you can use in the calculator, the state changes could be used for things like automated switch offs - whether it be to trigger DFS payments or just trying to help the cause (for those that don’t have smart meters).