I have to calculate the average temperature for each of the last seven days.
So I have been struggling with the query for the past days.
But, it does not work!!!
I made a simple query to see if there was any records at all in the table, by filtering on entity_id, oh yes, over 65k!
But when adding any conditions for the last_changed, the count was zero!
I got tired of it and installed the sqllite web.
And quering for my entity_id, I discovered that all dates was null!!
What the H is that?? Why are null? Most important, how do I get around this ???
I discovered that late yesterday evening…
But it’s not documented at all according to this documentation.
And all examples uses last_updated.
Hopefully I will get this working today.
It is the first part for deciding, meteorological, what season of the year it is, according to country rules.
Useless? Yes, but it should be possible!
SELECT avg(state) as state FROM states WHERE entity_id = ‘sensor.ute_norr_temperature’ and date(datetime(last_updated_ts, ‘unixepoch’, ‘localtime’)) = date(datetime(‘now’,‘-1 DAYS’))
By changing the ‘-1’ I can get the average temperature on every previous day I want, including today!
The rule for Spring is that the average temperature for the last seven days should be over zero °C and not before February 15, as an example.
I’m using the SQL integration, it would been lovely to have a comment section there. And maybe be able to create a SQL template, which can take parameters so when defining a sql entity it can be done by selecting a template and pass a parameter or two.
In my case an averageDayTemp template with the argument how many days back.
As now it was a lot of copy & paste, but now it is done!
And works like charm! Only the rules left!
