Energy consumption in last (sliding) 15 minutes

Configuration
2

I’ve managed to solve it myself! Woohoow! So proud…

In the integrations I’ve added the SQL integration (Home Assistant native). Added it twice as you have to add it per query and the result per query is immediately available as a sensor.

Hereby the query (replace entity_id for the second tarif):

SELECT state FROM "states" s LEFT JOIN "states_meta" sm ON s.metadata_id = sm.metadata_id where sm.entity_id = 'sensor.energy_consumption_tarif_1' and (last_updated >= Datetime('now', '-16 Minute') or last_updated_ts >= (unixepoch() - (15 * 60))) order by state_id asc limit 1

Then in my configuration.yaml I’ve configured a new sensor by template, including those SQL sensors:

sensor:
  - platform: template
    sensors:
      kwartierpiekverbruik_total:
        friendly_name: "Kwartierpiekverbruik (Totaal)"
        unit_of_measurement: "kWh"
        value_template: '{{ (((states("sensor.energy_consumption_tarif_1") | float + states("sensor.energy_consumption_tarif_2") | float) - (states("sensor.elektriciteitsmeter_kwartier_geleden_tarief_1") | float + states("sensor.elektriciteitsmeter_kwartier_geleden_tarief_2") | float)) * 4) | round(3) }}'
        device_class: 'energy'

I can now use sensor.kwartierpiekverbruik_total in my Lovelace dashboard and as a sensor value for automation triggers etc. And with a history as well.

2 Likes