Sum between two dates

Configuration Frontend
1

I have a couple of utility meters that reset at midnight each day. I want to compare those reading with the bill I get from the utility company every 1 to 3 months (different billing cycle for each).
I think I’ve seen something that lets me sum the saved values, taking into account that the counts fall to zero each night. (I.e. the counts for the ‘next’ day need to be added to those for ‘this’ day.)
(If that doesn’t work, I have a binary sensor that I can count the ‘off to on’ transitions and go from there.)
But what is puzzling me right now is how to get all of the samples starting from a given day and ending on a subsequent day.
I know that this can be done as the ‘History’ display does this.
I don’t want/need a graphical display as I’m assuming that I need to get the numbers for each day and sum them but how do start going about that?
Susan

2

I add my daily total cost to an input number just before midnight:

- id: 4245ef1b-75c8-4719-a76e-7139e3c78d6a
  alias: 'Daily Energy Use Message'
  trigger:
    platform: time
    at: '23:59:55'
  action:
  - service: input_number.set_value
    data:
      entity_id: input_number.cumulative_energy_cost
      value: "{{ states('input_number.cumulative_energy_cost')|float(0) + states('sensor.total_cost_today')|float(0) }}"
# there are more actions but that is the relevant part for you.

Then on my bill anniversary day (18th of the month) I reset it at midnight:

- id: 4b276634-3dc2-41bb-a8b9-48ab91941377
  alias: 'Reset Energy Use'
  trigger:
  - platform: time
    at: "00:00:00"
  condition:
  - condition: template
    value_template: "{{ now().day == 18 }}"
  action:
  - service: input_number.set_value
    data:
      entity_id: input_number.cumulative_energy_cost
      value: 0
# again, more actions follow but that is the bit you want.
3

If only our utility companies were that regular.
While we have a smart meter for the electricity (which means in theory is shodul nbe predictable), they still have someone walk around everywhere to read the gas and water meters and therefore the periods vary.
Ideally what I want is the ability to enter two dates and get the sum in between.
Susan

4

So, is there anything available that can turn this pseudo code into some form of reality?

start_index = history_values(sensor_name).index_at_date(start_date)
end_index = history_values(sensor_name).index_at_date(end_date)
last_value = <very high number>
sum = 0
for each index from start_index to end_index
    value = history_values(sensor_name).value_at_index(index)
    if value < last_value then last_value = 0
    sum = sum + (value - last_value)
show sum

Susan

5

I was thinking that you could maybe do it with an SQL sensor, but unfortunately the SQL sensor does not support templates in the query.

It does have a value template though. So maybe you could extract the data for the maximum length of time you expect (say a bit over 3 months) the refine it with the value template?

I have no idea how you would actually go about doing this though and suspect you may hit the 255 character limit for states.

And while the History Stats sensor does have start and end date templates it does not have a sum output. Also it only works on state data not LTS so you would have set your recorder purge keep days to over three months. Possibly creating database performance issues.

So that’s two ways it can’t be done. Only 9998 more to go…

I have not failed. I’ve just found 10,000 ways that won’t work.
Thomas A. Edison

6

Thanks Tom - what makes me think this should possible is that the ‘History’ graphic can show samples between to dates - e.g. ‘Last week’ or ‘Last Month’ or even arbitrary dates/times.
Therefore what I’m looking for is getting access to the data itself, rather than having it graphed.
Susan

7

(A little late but I’ve found the 10,001th way!!!)
I installed the SQL integration and the created 2 datetime entities (via the Helpers function) that contained the start and end date of the period in question.
To make things a little easier (but perhaps messing up the later maintenance) I looked for the 'metadata_id’s for the 2 datetime entities and also the entity that I wanted to sum (which was a ‘daily usage’ utility entity).
I then crated the following SQL entiy monster:

SELECT sum(daily_totals) FROM
(
	SELECT max(cast(states.state as float)) as daily_totals
	FROM states 
	WHERE metadata_id = 576
	AND last_updated_ts BETWEEN unixepoch(
		(SELECT state
		 FROM states
		 WHERE metadata_id = 785
		 ORDER BY last_updated_ts DESC 
		 LIMIT 1), 'utc')
		AND unixepoch(
		(SELECT state 
		 FROM states
		 WHERE metadata_id = 786 
		 ORDER BY last_updated_ts DESC
		 LIMIT 1),'utc') 
	GROUP BY date(last_updated_ts, 'unixepoch', 'localtime')
);

where the literal numbers are the metadata_id’s mentioned above.
This SQL query uses the entered dates for the ‘BETWEEN’ clause, groups the values by date and then takes the max (for each date) before summing the results.
At least it gives me the same values that I got from an sqlite3 SQL script that I wrote that talked directly to the HA database.
In case anyone else is trying to do something similar.
Susan