[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
      mdb_url: mysql://user:mypassword@localhost/homeassistant
      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.

Thanks for your reply!

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

See https://www.home-assistant.io/blog/2015/05/09/utc-time-zone-awareness/

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
Please help me?

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!