Hi! I have a question regarding the Statistics Sensor. I’ve got a Sonoff POW which delivers consumed energy of the attached device via MQTT (Tasmota FW). I want to calculated the projected yearly costs using the average consumption over the last 30 days (average * 365 * price).
The Sonoff delivers the value for yesterdays consumption. So, I thought, I might get the average with using the Statistics Sensor on that sensor. The problem is I don’t know what to set the sample size to, as the MQTT sensor receives the same value multiple times throughout the day. So, I just want to analyzed all values over the last 30 days, maybe grouped by day. How can I receive the average value I need?
Thanks! I did look in to the history_stats sensor, but it seems to need to track a specific state, which I do not have.
Anyway, I just found a solution using the new SQL Sensor:
sensor:
- platform: sql
db_url: !secret recorder_db_url
queries:
- name: Average Energy Consumption
query: >
SELECT ROUND(AVG(avg_per_day),2) 'value'
FROM (
SELECT AVG(state) AS avg_per_day
FROM homeassistant.states
WHERE entity_id = 'sensor.sonoff_power_yesterday'
AND state != 'unknown'
AND state != ''
AND created > NOW() - INTERVAL 1 MONTH
GROUP BY DATE(created)
) avgs;
column: 'value'
unit_of_measurement: kWh
This, then, gives me exactly the average value I was looking for.
I needed a sensor for showing how much energy our water heater was using so far this day and how much it had used the previous day.
Had some issues with how to get MySQL working as I wanted.
HASS store the dates as UTC as far as I can understand, and for it to report correctly I had to adjust the timezone accordingly. Perhaps there is some way to store the values in my current timezone, but I haven’t bothered finding out yet.
- platform: sql
db_url: !secret recorder_db_url
queries:
- name: Hotwater energy yesterday
query: >-
SELECT MAX(state) - MIN(state) AS 'value'
FROM (
SELECT CAST(state AS DECIMAL (10 , 2 )) AS state
FROM states
WHERE entity_id = 'sensor.hotwater_g2_energy'
AND state != 'unknown'
AND state != ''
AND last_updated BETWEEN
CONVERT_TZ(curdate(), '+02:00', '+00:00') - INTERVAL 1 DAY
AND CONVERT_TZ(curdate() , '+02:00', '+00:00')
)t;
column: 'value'
unit_of_measurement: kWh
Some words of advice regarding SQL sensor:
the column state is a varchar and depending on type of SQL database it could calculate AVG() differently. I’m using MIN() and MAX() in my queries and MySQL would sort lexicographical, meaning 9.99 > 15.1. Hence the cast in my query.
The column created does not have an index, whereas last_updated do have an index. If you have a lot of data recorded and store it for a long time, the queries will be slower if you use columns without indexes.
Hello I wanted to ask you a question, in addition to pasting the sript with the modified sensor names, I have to create the folder in the database. Or does the script do it automatically?
Eventually, could you show me how? Thank you
I attach my configuration
name: Average Energy Consumption
query: >
SELECT ROUND(AVG(avg_per_day),2) ‘value’
FROM (
SELECT AVG(state) AS avg_per_day
FROM homeassistant.states
WHERE entity_id = ‘sensor.energy_yesterday’
AND state != ‘unknown’
AND state != ‘’
AND created > NOW() - INTERVAL 1 MONTH
GROUP BY DATE(created)
) avgs;
column: ‘value’
unit_of_measurement: kWh
Well, there are no “folders” in a MySQL database. None that I know of, at least. For the SQL sensor to work you do not need to modify the MySQL instance itself. You only need to make sure that Home Assistant stores the energy sensor’s values (in your case “sensor.energy_yesterday”) in the database. For this, take a look at the Recorder component.
Then you should watch home assistants database with a MySQL browser. I am using HeidiSQL. Just do a simple SELECT on the states table and see if your sensor comes up after some time.
- platform: sql
db_url: !secret recorder_db_url
queries:
- name: Average Energy Consumption
query: >
SELECT AVG(state) AS avg_per_day
FROM homeassistant.states
WHERE entity_id = ‘sensor.energy_yesterday’
AND state != ‘unknown’
AND state != ‘’
AND created > NOW() - INTERVAL 1 MONTH
GROUP BY DATE(created)
) avgs;
column: 'value'
unit_of_measurement: kWh
I just discovered the SQL sensor through this old conversation and @oey words of advice on casting strings to numbers, last_updated column index and UTC storage were all spot-on. For those like me that use the default SQLite database the date time syntax seems to be quite different, so sharing the SQLite version here. I display the minimum whole-house power draw since midnight local time - a good way to notice when something has been left on accidentally overnight like a TV, closet light, gutter heater.
- platform: sql
queries:
- name: Min today
query: >-
SELECT min(CAST(state AS NUMBER)) as state
FROM states
WHERE entity_id = 'sensor.ted_mtu1_power'
AND last_updated > datetime(datetime('now','localtime'),'start of day','utc');
column: 'state'
unit_of_measurement: W