I am puzzled… and have no idea what to do.
There is a sensor sensor.from_netz_daily that has the value of the energy of the day. It is working because I am seeing the values in graphs and in the history.
I use it in the SQL query to collect the values from 1 day ago, 2 days ago etc.
(I am using this also for energy sold, and heat pump… and none of the queries is working)
Here is the query
SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.from_netz_daily'
AND last_updated_ts <= strftime('%s', 'now', 'start of day', 'utc')
ORDER BY
last_updated_ts DESC
LIMIT
1;
here the query for 2 days ago
SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.from_netz_daily'
AND last_updated_ts <= strftime('%s', 'now', '-1 day', 'start of day', 'utc')
ORDER BY
last_updated_ts DESC
LIMIT
1;
Can please anyone tell me where to look or do?.. as mentioned In looked in the graphs and it stopped after HA restarted after updates
Home Assistant (at least on my setup, which has not yet been updated for January) performs a reset on daily (energy) sensors, and this is happening about 15 seconds before midnight.
When I run your SQL on my own figures for a similar sensor, the very last figure I get for yesterday is ‘0’, which is actually the first reset value of the new day (but timed for 23:59:46 - hence before 00:00:00 of today).
I ‘fixed’ this by subtracting 30 seconds from the test value.
WHERE
states_meta.entity_id = 'sensor.from_netz_daily'
AND last_updated_ts <= strftime('%s', 'now', 'start of day', 'utc')-30
that is a great idea.
I tried … nothing changed… I still see 0.
Question: do the SQL value take time to show or would I see the results immediately (if that change would be correct for me?)
What bother me: it was working as intended before… 1.5 years long…
I hardly know any SQL, and this is quite new to me. However, I find the SQLite Web add-on really good and easy to use. I copied your SQL over and ran a query to see just what came up. This is how I write and test all my SQL before using it in an SQL sensor or in Node-RED.
I needed to change your query to a sensor I know generates ‘energy today’ readings, and I removed the LIMIT so I could see the table returned. Insightfull.
I added back in the last_updated_ts, as well as the outcome of the strftime() so I could see what that was generating.
For me it is clear that 15 seconds before midnight the value is being reset, to 0, and hence the LIMIT returns the last one I don’t want.
The 15 seconds comes from looking at the history of this sensor, although it is easy to check Unix seconds.
In other SQL code I use I pull in hourly statistics records for the day, and I use both an end time and a start time (just take 24*36000 seconds off the start of today) to block ‘yesterday’ and I use a ‘max’ to get the value I want - you could try LIMIT 3 and MAX
I modified the query … eg to get the result 2 days ago…
SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.from_netz_daily'
AND last_updated_ts <= strftime('%s','now','-1 day','start of day')
AND last_updated_ts >= strftime('%s','now','-2 day','start of day')
ORDER BY
CAST(states.state as numeric) DESC
LIMIT
1;
Now it works perfectly!
I will publish this in a separate thread in case someone needs .
Still dont understand why it stopped working as I had before… but I have the solution and I understand better actually what I have now
