Utility Meter - Day minus 1 consumption ideally till the same time

Hello,
I am using an utility meter connected to a sensor with Efergy. I am able to estimate consumption by time period, estimate EOM bill and many other things by using simple templates.

It all works good, BUT (there is always a but), now I want to add one more thing (wife request). I want to compare cumulative consumption of a period (i.e., today until 3.30pm) with the same time expand the period before (i.e. yesterday until 3.30pm). The idea is to be able to see whether I am or not aligned with intraday consumption. Does this even make sense? How would you go about it?

My last resource would be to create a Py script running vs. HASS DB and publishing the data into MQTT, but I am pretty sure there should be something more simple

Thanks!

I would create an input number to store the 3:30pm value.

Then trigger an automation at 15:30 every day (time trigger) and use the input_number.set_value service in the actions to store the current consumption in the input number.

There are a number of ways to display this. A 168hr history graph of the input number would show you the daily 3:30pm value for the last week as a line graph.

I use the mini-graph card, (this is aligned on midnight):

Screenshot_2020-09-16 Overview - Home Assistant

Very ingeniousness solution indeed, it´s kinda of a quick fix. What about adding more data points?

I´d like, if possible, to have more than one check point in the day without having to create 24 input numbers and 24 automatons (one per hour). Ideally i want to get to a chart like the one below (made in excel). You can see how the day is evolving with more comparison time periods (in my case the lighter the color the older).

It may be too complex, but I want to get your thoughts.

Hmm. Tricky. The problem is that the history graph (and mini-graph) only have the one time axis.

You would have to add 24hrs to the timestamp of readings taken yesterday to graph them alongside today’s values. And as far as I’m aware there is no way to do that in home assistant.

Forget about the chart then, it would be enough to have a number. Something like this.:

Cumulative today till now: 4kwh
Cumulative yesterday same time: 3.68kwh
Cumulative same day last week same time: 4.14kwh

Any ideas? Following tom_I logic, with an array an automation it could be relatively simple no?

Again, thank you

So I have two approaches, whatever yield results first I will share:

  • Easy lazy way. Use grafana to create a consumption dashboard and using a plugin of this sort for relative time shifting
  • Cool way. Use a SQL sensor and left join with a relative shift (it´s two line codes in sql)

If you’re using the addon you can only load plugins from the grafana website.

I’d definitely be interested in seeing this.

Still working on it :(. @tom_I, you were totaly right, Grafana plugin. I have not put any time today since I am mostly focused on understanding why my CT clamp is giving me +60% consumption low consumption period.

Hello,
I solved using the SQL option.

The idea is to create a sensor using the SQL integration that always return the latest value of the shifted time period. For example now is 9.32 AM 09/21, the sensor will return the measurement of the 9.32 AM of the 09/20. That sensor is added to your config.yaml and using a history chart you can plot current value plus any shifted series (the sensor will record its history as any other one so you can plot a linear chart)

This is my SQL code:

SELECT
	max(strftime('%Y%m%d %H:%M',created,'1 day')) as created
	, state
FROM
	states
WHERE
	entity_id = 'sensor.daily_consumption' 
	and created between datetime('now','-1 DAY','-1 hour') and datetime('now','-1 DAY')
and domain = 'sensor';

And this is how the sensor looks like:

  - platform: sql
    scan_interval: 120
    queries:
      - name: 1ds consumption to now
        query: "select max(strftime('%Y%m%d %H:%M',created,'1 day')) as created, state FROM	states WHERE entity_id = 'sensor.daily_consumption' and created between datetime('now','-1 DAY','-1 hour') and datetime('now','-1 DAY')	and domain = 'sensor';"
        column: 'state'
        unit_of_measurement: 'KWh' 

The end result is this one. Sorry I still don´t have a ton of data to plot -7d or even a full day but you can see the green line diverging during the first hours of the day. Once I have a full week I will also add the -7d line
consumo

Not that complicated at the end!

1 Like

Last update, here is how the card looks like with two shifted periods: (there is an ugly spike related to when I plug the clamp in -7d)
Consumption Chart

1 Like

That’s awesome. Bookmarked for future use. Thanks for sharing.