REQ: Add type: "change" for statistics

[rrozema]

For entities that have a state_class of "total" or "total_increasing", the recorder creates an ever-increasing sequence of [sum] values in the statistics table. Using the currently available type options "sum" and "state" we can only show this ever increasing value (= sum) or the actual meter reading at the time of measuring (= state).

The difference between one statistics row [sum] and it's predecessor's [sum] value is however the amount that the measurement changed between the both recorder runs, which can be a much more interesting value to put into a graph. [sum] is only set for statistics that have has_sum set to 1 in statistics_meta, so this determines if the "change", "sum" or "state" options should be available.

The "change" value between any two statistics points can be retrieved by subtracting the [sum] value of the statistic point (start_ts) at the start of the period from the [sum] value of the statistic point at the end of the period. An increase will result in a positive change value, a decrease in a negative change value. For example to query the change values for all available hourly statistics data points from the start of this day for one single entity we can use this query:
select strftime('%d-%m-%Y %H:%M:%f', se.start_ts, 'unixepoch') as dt, se.[start_ts], se.[sum] - ss.[sum] as [change], m.unit_of_measurement from statistics_meta m inner join statistics se on se.metadata_id = m.id left outer join statistics ss on ss.metadata_id = se.metadata_id and ss.start_ts < se.start_ts and not exists (select * from statistics ss2 where ss2.metadata_id = se.metadata_id and ss2.start_ts < se.start_ts and ss2.start_ts > ss.start_ts) where m.has_sum = 1 and m.statistic_id = 'sensor.electricity_meter_energy_consumption_tarif_2' and se.start_ts >= strftime('%s', 'now', 'start of day') order by se.start_ts;

If you do start implementing this, please be careful that the recorder can not always provide a row for each data point: gaps will exist in the start_ts seqeunces. So we can never assume that the previous data point has the current data points' start_ts minus 1 hour. We always need to search for the previous data point's start_ts value. This happens for example when a sensor was temporarily unavailable at the time the recorder created the data point.

[rrozema]

I tried creating a graph that demonstrates what I would like to achieve. This graph shows the costs and compensations for the entities in my energy dashboard. The yaml for it is below. In this yaml I would like to change all type: sum into type: change so that the values on the y-axis will show the total costs for each day, as opposed to the total of the cumulative costs as is shown now.

          - type: custom:apexcharts-card
            graph_span: 2 week
            stacked: true
            series:
              - name: gas
                entity: sensor.gas_meter_gas_consumption_cost
                type: column
                statistics:
                  type: sum
                  period: day
                  align: end
                group_by:
                  func: last
                  duration: 1 day
              - name: water
                entity: sensor.connectix_watermeter_totaal_cost
                type: column
                statistics:
                  type: sum
                  period: day
                  align: end
                group_by:
                  func: last
                  duration: 1 day
              - name: energy low tarif
                entity: sensor.electricity_meter_energy_consumption_tarif_1_cost
                type: column
                statistics:
                  type: sum
                  period: day
                  align: end
                group_by:
                  func: last
                  duration: 1 day
              - name: energy high tarif
                entity: sensor.electricity_meter_energy_consumption_tarif_2_cost
                type: column
                statistics:
                  type: sum
                  period: day
                  align: end
                group_by:
                  func: last
                  duration: 1 day
              - name: energy out low tarif
                entity: >-
                  sensor.electricity_meter_energy_production_tarif_1_compensation
                type: column
                statistics:
                  type: sum
                  period: day
                  align: end
                group_by:
                  func: last
                  duration: 1 day
                invert: true
              - name: energy out high tarif
                entity: >-
                  sensor.electricity_meter_energy_production_tarif_2_compensation
                type: column
                statistics:
                  type: sum
                  period: day
                  align: end
                group_by:
                  func: last
                  duration: 1 day
                invert: true

Using type: sum this results in this picture:

whereas I would like each column to show only the changes in the values, i.e. that day's costs and compensations.

[rrozema]

If I understood it correctly, pull request core/92823 adds the statistics type "change" into the core. This performs the calculation of the differences directly in the back-end: You could just sum up the "changes". @RomRider, I hope this helps you adding this way of reading the "running total" statistics.

[rrozema]

I found that this is actually already implemented in the dev branch: PR #555 and issue #552.