I’m trying to run a query to get the status of a sensor at the start of the day. What I’m trying to do is to report daily energy use. My inverter (SMA Sunny Island) only reports total energy use over time. So I need to manually create a sensor that will substract total energy use at the beginning of the day from current total energy use.
This is my config (3 sensors - 1 to read current total energy use - 1 to read total energy use at the beginning of the day - and 1 to substract both).
sensor:
- platform: mqtt
name: "Total Energy"
state_topic: "solar/inverter/1260042985"
unit_of_measurement: "Wh"
value_template: "{{ (value_json['values'].EgyCntOut * 1000) | round(0) }}"
unique_id: total_energy
- platform: sql
queries:
- name: "Total Energy BOD"
unit_of_measurement: "kWh"
query: "SELECT * FROM states WHERE entity_id = 'sensor.total_energy' AND last_updated > datetime(date('now', 'start of day')) ORDER BY last_updated ASC LIMIT 1;"
column: state
template:
- sensor:
- name: "Daily Energy"
unit_of_measurement: "Wh"
state: >
{% set current = states('sensor.total_energy')|int %}
{% set bod = states('sensor.total_energy_bod')|int %}
{{ (current - (bod * 1000)) | round(0) }}
The SQL sensor is not returning any data and reports as ‘unknown’. I think there must be something wrong with how I wrote the SQL.
Any thoughts on what I’m doing wrong?