Mean temperature for current day - SQL

Hi,

I want to show the mean temperature for the current day with the SQL syntax.

When I am on the SQL DB Browser, I can execute this code and it’s work :

SELECT AVG (state) FROM states WHERE entity_id = 'sensor.out_temp' AND last_updated > datetime(datetime('now','localtime'),'start of day','utc');

But, when I want to format it for HA, this code don’t work :frowning:

sensor:
  - platform: sql
    queries:
      - name: Mean Out
        query: SELECT AVG (state) FROM states WHERE entity_id = 'sensor.out_temp' AND last_updated > datetime(datetime('now','localtime'),'start of day','utc');
        column: 'state'

With this error message :

2019-07-29 17:39:03 ERROR (SyncWorker_3) [homeassistant.components.sql.sensor] Error executing query SELECT AVG (state) FROM states WHERE entity_id = 'sensor.out_temp' AND last_updated > datetime(datetime('now','localtime'),'start of day','utc') LIMIT 1;: "Could not locate column in row for column 'state'"

And I don’t know why … Can you help me ?

Is this for sqlite?

On mysql i get an error about the datetime query.

If i remove this and query only

SELECT AVG (state) FROM states WHERE entity_id = 'sensor.out_temp'

i get a result with column name ‘AVG (state)’

Your sensor references column: 'state'

Maybe try a

SELECT AVG (state) as state FROM states WHERE entity_id = ....

Why not use https://www.home-assistant.io/components/statistics/

1 Like

Thank you ! It’s work !
Little question : Is not the best practises to do that ?

Because, I only make the mean for 2 hours ou 24 hours, but not since the start of day until now, except if I wrong read the page of home assistant…

Do you know how many minute between each SQL request ?

If it works for you, why not?

The default is 30 seconds, i think.
You can change it with the scan_interval option.

sensor:
  - platform: sql
    scan_interval: 600
    queries:
      - ...

Yes, it’s work, but I just want to limit the request on the database. Thanks a lot for the scan interval informations.