SQL issues - sensor returns 'unknown'

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?

did you run the SQL on your DB directly to verify output?
There are free plenty tools that connect to db for this

Ok, downloaded sqlite3 and ran the query. Apparently some of the state values in the db are ‘unknown’, which is weird. anyways, I fixed it by excluding any ‘unknown’ values.

There are a few things that I noticed:

  1. the timestamps in the database don’t match with the timestamps in Home Assistant. The ones in HA reflect my local time, but the ones in the database are UTC. So when my query has a time modifier that says ‘beginning of day’, that’s not really correct in local time …? Or does HA translate time zones?
  2. there are far fewer history entries for this particular sensor than should be the case. I don’t really understand that. The sensor gets updated every 30 seconds or so. I guess not every update gets recorded in the database? Maybe only if something changes?
  1. HA translates time zones, this you can see a.o. here (I am CET)
  2. Not sure and depends on sensor and recorder settings, note there is also statistics tables