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
SELECTcreates 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
SELECTgenerates a table of differences between end and start (which is the energy used) for each day; - and the first
SELECTaverages 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:
…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.
