# [SOLVED] Statistics Sensor and average over time (for calculating power consumption from Sonoff POW)

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?

… or am I thinking too complex?

Thanks anyway!

I think there is a component for this:

Might take some ingenuity to get the data you want.

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.

4 Likes

Ah yes, that would work too. Nice find.

1 Like

Do you mind sharing your configuration and automation?

@badstraw, the configuration he is using is listed:

There is no automation, it’s just a single sensor that outputs the average cost per day over the last 30 days.

1 Like

Sorry for not replying sooner. Yes, @petro is right, it is just a sensor and that is the configuration I use.

1 Like

@qrioniclabs can you please share “sensor.sonoff_power_yesterday” configuratoon

Thanks for pointing me in this direction.

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.
2 Likes

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

• platform: template
sonoff_pow_1_yesterday_cost:
friendly_name: Yesterday Cost
entity_id: sensor.energy_yesterday
unit_of_measurement: “€”
value_template: >-
kWh
{{ states.sensor.energy_yesterday.state | multiply(0.1897) | round(2) }}

sensor:

• platform: sql
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.energy_yesterday’
AND state != ‘unknown’
AND state != ‘’
AND created > NOW() - INTERVAL 1 MONTH
GROUP BY DATE(created)
) avgs;
column: ‘value’
unit_of_measurement: kWh

It’s just an MQTT sensor for the Sonoff POW information:

``````- platform: mqtt
name: "sonoff_power_yesterday"
state_topic: "tele/sonoff_pow_a/SENSOR"
value_template: "{{ value_json['ENERGY'].Yesterday }}"
unit_of_measurement: "kWh"``````

@Nicolo:

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.

As far as i know, HA internally only uses UTC and that’s the only way it should be.

1 Like

On reboot, though, I can not see the newly created sensor in the home assistant. Do you have any suggestions?

can someone help me?
I write:

``````- 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
``````

in configuration.yaml
i write:

config:
db_url: !secret recorder_db_url

…but i don’t see a sensor

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
``````

I searching for a solution for a sensor that presence the yesterdays average temperature from a input sensor.

How could I archive this?

thx a lot!