Statistics sensor to calculate average power consumption and interpolate

[x4N70pHyLL]

Hey,

I read this thread while trying to figure out a solution to my problem.

I want to do the following:
Interpolate the electric consumption for a year based on former consumption.
by

• setting the start date - in this case when the electricity contract started
• having a statistic sensor to sum up the consumption since then (see code)
• having a template sensor to divide by the days since then and multiply by 365 (to be done)

I do get errors in the configuration.yaml, I assume I have the concept right, if I put in a number, it works. But I do not know how to fix it, tried several things

  - platform: statistics
    unique_id: strombezug_seit_startdatum
    entity_id: sensor.jahresverbrauch_strom
    name: "Strombezug seit Startdatum"
    state_characteristic: change
    max_age:
      days: '{{ ( as_timestamp(now()) - as_timestamp(states(input_datetime.datum_start_stromverbrauchsrechnung)) ) / 86400 }}'

Any ideas? Here I do get an error that it expects a float.
“days: !!float {{…”
does not work. Removing the singe quotes doesn’t either. Using double quotes neither. Piping to float neither.

[Didgeridrew]

In addition to the template issues, the overall concept is flawed. The Statistics integration is limited to the purge keep length of the Recorder. This defaults to 10 days.

For reference, if this was actually possible, the template would be:

{{ (now() - states('input_datetime.datum_start_stromverbrauchsrechnung')|as_datetime|as_local).days }}

[x4N70pHyLL]

Cold you recommend another method? I would like to make use of the same seamless polling of values as it is now in the history tab (there recorder is solid blue and history is more light blue), is there a way of using that? I wanted to avoid writing it to a database and pulling it again.

Second question goes to the “template issues”, would you mind telling them or was your comment more like "I don’t know what’s wrong with the template, but it won’t work anyways?

Thanks

[parautenbach (Pieter Rautenbach)]

@x4N70pHyLL

Cold you recommend another method?

Is this an energy sensor already used in your energy dashboard? Does it have a state class? If so, you can create a SQL sensor to calculate what your want from the DB directly.

[Didgeridrew]

What @parautenbach said…

I’m not that well versed with SQL, but there may be some helpful information in the data science portal:

Long- and short-term statistics | Home Assistant Data Science Portal

[x4N70pHyLL]

I do have a solution now:

I created several meters that derive from the actual consumption meters and reset daily:

• Consumption overall day
• Consumption Wallbox day
• Consumption Heatpump day

Then a template sensor to calculate “the remaining”:

• Consumption Home day (=Overall - Wallbox - Heatpump)

Then there is one meter each that collects these values and is only reset manually (to meter each consumption since a specific day, the start of my energy contract)

From there I created another template sensor each that calculates the

“days since start of calculation” (that is the day where my energy contract started and)

and divides the consumption of each meter by that and multiplies it by 365.

Result: I do have an estimation of my Wallbox, Heat Pump, Rest of the House and total consumption towards a whole year → these are values I am used to as you have to deal with them when signing an energy provider contract.

Seems to work quite well, however, I do seem to have issues with the template calculation for the “remaining house”.

The sensor value itself seems to be valid, but it goes up and down during the day, you can see it especially if much energy is used, e.g. charging of the car (which comes into my equation via “total amount” and should be eliminated again by substracting the"callbox amount".

This leads to the estimation calculation being wrong as it does not take the “sum”, but the “change” of the “Home consumption day”. I cannot change that.

Any ideas here?

Do I need to set my meter in a specific way?

This is the “Home consumption day” example:

That results in the yearly estimate of “Rest of House” going up, even it shouldn’t as the Wallbox consumption is eliminated in the equation:

[parautenbach (Pieter Rautenbach)]

Without seeing you actual YAML and histories for the sensors used, it’s hard to say.

[x4N70pHyLL]

Maybe to put my problem in a short explanation:

• I measure the daily consumption of “Rest of Home” with a template sensor that is calculated by the difference of my total consumption and the biggest consumers (Wallbox & heat pump), it resets daily

• This sensor has deviations up and down during the day (I suppose because of the different sources are not 100% in sync) - see my below

• I have a meter helper that measures this daily template sensor over a longer time period. This sensor displays wrong values as it measures the “change” from the template sensor, which is significantly higher (because it has the said deviations) as the actual consumption

How can I set it up I na way that only the total amount is measured and not the “up-and-down”?

EDIT: The template sensor code, just to clarify:

{{
float(states('sensor.verbrauch_strom_netzbezug_tag'),0)
-
float(states('sensor.verbrauch_wallbox_tag'),0)
-
float(states('sensor.verbrauch_warmepumpe_tag'),0)
}}

[Fanful]

Could be because one of the sensors is periodically unavailable, if you don’t have availibility template set up for that template sensor.

So for example let’s say your heat pump sensor becomes unavailable for whatever reason, the template sensor is calculating like the heat pump sensor state was 0 (and so the result jumps up, if the previous value of heat pump was higher, then it jumps back down when the heat pump sensor becomes available again).

Do you have availability template set up for that template sensor?

Template

If not, try adding it. This stops the sensor from calculating the value if the availability template result is false. Quick example:

        availability: "{{ states('my_sensor_1') | is_number and states('my_sensor_2') | is_number }}"

This should stop the template sensor from calculating if either my_sensor_1 or my_sensor_2 states are not numbers.

[x4N70pHyLL]

That could be another reason, but one seems to be more obvious to me (especially since the deviations increase while consuming more power and I have no indication that any of the sensors is not available from time to time:

The “overall consumption” sensor increases not in exactly the same point in time as the other sensors that are substracted do.

Example:

Wallbox consumes

• overall consumption increases
• Wallbox consumption increases
• “Rest of house” SHOULD stay the same as these two values are substracted. As it it happens/updates the sensor not exactly on the same millisecond, I have these “down-and-up-deviations” that my associated meter interprets as additional consumption

[x4N70pHyLL]

Any ideas here? It seems I have to ensure that only “sum” is taken, not the “change”

[Fanful]

Have you considered calculating the “rest of the house” value at the every end of everything? Since you only need the end value for the whole period and not for each day.

So, instead of calculating this on the daily sensors (daily total minus daily water pump and wallbox) you could calculate it on the utility meters for the whole period (total energy used since X date - total energy used by water pump since X date - total energy used by wallbox since X date).

As you mentioned you do have those meters set up already:

@x4N70pHyLL

Then there is one meter each that collects these values and is only reset manually (to meter each consumption since a specific day, the start of my energy contract)

So yes, this new meter wouild also jump up and down, but it wouldn’t matter, because you would not have to manually add daily values. You would only use the current state.

[x4N70pHyLL]

Thanks for your reply and yes, that is exactly what I did today to test it. It will probably erase the measurement errors I have at night when all meters reset (almost, but not exactly at the same time which leads sometimes to even bigger ups and downs around 0:00).

However it seems not to correct the sawtooth shape of the meter when consumption is high - I tested this with the daily meters as HA switched from recalculating when one entity changes to recalculating every minute (this is also mentioned in the template sensor documentation). It looks much better then but stillt goes up and down and thus the “change” is much higher then the actual consumption.

So yes, only using the state/value would help, but it is not possible in the statistics card for example, so I don’t know how I could display my consumption from e.g. yesterday (I can only select “change” there, not state/value)

[Fanful]

You need to decide what you actually want to achieve. Initially you wanted consumption per day for fixed period since a specific date. So I suggested a workaround for that. But now you want consumption for each day, this is a different requirement.

By the way I’m my proposed sensor, state from yesterday would not give you your consumption from yesterday anyway. It would give you consumption up until yesterday. You would need to use change, but then again the whole point was to avoid using that

[x4N70pHyLL]

@Fanful Sorry for the mess that is more like a “side quest” that I want to achieve, of course the main goal is to calculate the consumption for a defined (not fixed) period, that has not changed.

However, doing it in a “correct” manner should enable me to du calculations such as my example, that was my point.

So I rephrase my question: Any idea how to achieve that (to be able to use the sensor for any period like the others that are directly derived from their sources?

The rest of your proposal is in place but I did not have the chance to analyze it yet with a high load (where the errors became most obvious in the past), I hope I can do that today with charging my car.

[Fanful]

The only other idea I have is to update the sensor only once a day, shortly before midnight. But this is more error prone, if for whatever reason something is not available at the time of this update. I don’t have any other, better ideas how to handle this.

[x4N70pHyLL]

So as announced here is my detailed feedback:

• changing to overall values being used for calculation does help against the crazy stuff around midnight, that is gone

• I do have funny jumps here and there, I correlated it to restarts of HA which makes at least a little sene and overall (as I cannot use “change” anyways) it doesn’t matter for the calculation
  

  

• The calculation makes kind of sense (the drop is from the change of the calculation base from daily to overall) and it does not significantly change since then even though using the wallbox - however, I do not have a good method to calculate yesterdays consumption like mentioned in my post above
  

  

• The sawtooth is still there but is quaked out at the end of the consumpton, here is an example of load during the day, only the oven leads to increasing of the meter, the rest seems to get leveled out, which is correct
  

  
  
  Looking at the detailed graphs still leaves me kind of confused, it seems to be updated every minute but has strange values still.
  Example heat pump in detail:
  
  
  
  Example wallbox in detail:
  
  

[ckha (CK)]

It seems the way is to use InfluxDB (just go with v2, not the default v1 like me) or Victoria Metrics (I tried to migrate yesterday from Influx v1 and it went okay but I prefer Influx’s Flux QL though so I’m stuck with Influx v1 for the time being).

It works great with the daily meter to get average consumption.

I also did more complex stuff like estimated energy usage over given hours of the day based on historical usage - I wanted to be able to tell if my batteries will last the night based on historical usage.