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