Anyone interested in a reports for Utility Meter? ...or maybe historical sensor value?

Tags: #<Tag:0x00007fc41a196eb0>

Dear community members and developers, please take a look and vote or comment on RFC for Reports.


RFC for Utility Meter Reports in Home Assistant

Purpose of this change request - to show bar chart or text values in lovelace panel with sensor values aggregated by period (i.e. hour, day, month, quarter, year) in past.

Constraints:

  • To improve performance for long reporting periods - only single value per reporting period should be stored.
  • To reduce amount of change in the UI - reuse existing charts and lovelace panels functionality
  • Functionality must be part of standard HA installation without any integrations to external systems. By this we assume that user should not do any administrative work except a) Supervisor install component b) update configuration.yaml.

New changes marked in bold or in * * stars * *.

Change 1:
Revision 2.0 - Instead of utility_meeter modifications, lets create aggregate component.

For each sensor calculate and record in to states values per period.
Proposed configuration of component:

aggregate:
  - period: daily
    calculate: 
      - delta
      - last
    entity_globs:
      - sensor.*_total_offpeak
  - period: hourly
    calculate:
      - average
      - min
      - max
    entity_globs:
      - sensor.*_total

Possible calculation functions: min , max , count , average , first , last , stdev , sum .

Source data selection logic:

  1. minimal purge days = 1, then this constraint does not affect period daily, hourly.
  2. by default purge days = 10, then we can check if weekly data exists for the sensor. If not see 3.
  3. If recorder purge days < sensor aggregation period - perform aggregation of the maximum possible period (i.e. daily or weekly) and record aggregation for required period based on aggregation from smaller period (i.e. weekly aggregated value calculated from 7 daily aggregates).

Dependency on recorder.purge_keep_days value from configuration.

Caused by :

Change 2:
Revision 1.0

Currently last sensor value received with states(‘sensor.foo_daily_total’). To receive sensor value in the past or in future (yes! some integrations may feedin future values) we need something like states_history(‘sensor.foo_daily_total’, relativedelta(months=-1)). The relativedelta function is very flexible and can work with any utility meter report period.

Function will return first value before the date/time passed with relativedelta. For example: if there is value 5 for 31-Dec-2020 then for 1-Jan-2021 or 20-Jan-2021 function will return 5 and for 20-Dec-2021 function will return value recorded before 31-Dec-2020.

Caused by:

Change 3:
Revision 1.0

To allow textual representation of the report values any HA user can create a custom lovelace panel. Panel will use Jinja2 template and Change2 to retrieve historical values for specified period.

Values are stored as a separate sensor state and could be used in charts out of the box.

No code changes will be required for this functionality.

Caused by:

Could be supported by:

— END OF RFC —


Dear community members, please review and like if you agree with RFC. Please comment if something is missed or you disagree - I’ll update this RFC based on your proposals.

There was a feature request already about the reports: Why The Heck: Does Domoticz have a better utility meter report then HA?

Let’s maybe move this proposal there

Edit: or maybe not - I have noticed now that it was in the “What the heck?!” subforum and probably no one looks there any more

1 Like

Change 1:
Version 1.0 - Cancelled.

utility_meter:
  foo_daily_total:
    source: sensor.pump_total
    cycle: **monthly** or **daily**, etc.
    **report: true** # false - by default 
    tariffs:
      - peak
      - offpeak

If the report parameter is True then utility_meter.next_tariff will write in to DB new state value for item called foo_daily_total_report.

Utility Meter component documentation should contain warning to exclude _report metrics:

recorder:
  purge_keep_days: 1
  exclude:
    entity_globs:
      **- sensor.*_report**

Change 1 does not require any changes in the UI and will allow to build chart on _report values immediately after the PR release.


Reply to - Utility meter reports (daily/monthly/yearly) - #26 by maxwroc

You understand it correct - keep changes to minimum and reuse existing functionality to pass the end of period values to existing UI.

If we create new table or do another step-a-side movements then we will loose all power of existing functionality and probably will had to re-invent the wheel :slight_smile:

Lets see what we have for RFC - Change 1:

UtiliityMeeter already write sensor value per each tarif, i.e. _peak, _offpeak. Then looking at

Why not create another state stream _report? When async def async_reset_meter(...) called we write self._last_period (end of period value) in to the state.

Then it will be possible to exclude old _report values from cleanup and create a chart based on this values, i.e. for monthly it will be 12 records in db, for daily it will be 365 state records per year, etc.

Yearly daily chart will be drawn much faster from 365 records than from thousands or state values received each 15 seconds.

I thought a bit more about Change 1 and I think I found a better option - to extend recorder to aggregate expired values.

Then recorder can be extended with config like:

recorder:
  include:
    ...
  aggregate:
    - period: daily
      calculate: delta
      domains:
        - meeters
  	  entity_globs:
        - sensor.*_total_offpeak
    - period: hourly
	  calculate: average, min, max
  	  entity_globs:
        - sensor.*_total
  exclude:
  ...

Possible functions: min, max, count, average, first, last, stdev, sum.

Values aggregation functionality depends on recorder activity and I don’t think it could be independent component.

Functionality intersects with existing statistics module (Statistics - Home Assistant), but it does not pretend to replace it:

  • for large volumes statistics module can be used on aggregated data
  • use statistics module for short time periods or in situations when changes in recalculation formula must be reflected in UI immediately.

This change for reporting looks to me more useful because

  • can be applied to any sensor;
  • reduce amount of calculations per UI request
  • keep old data unchanged and its important for reporting (i.e. invoicing process has no recalculations in past, instead and delta added to the next period invoice - in this case utility_meeter.calibrate can be used… but its a separate story :wink: )

Please vote for this option if you like it.

Caused by:

I was wrong about aggregate as a separate component - it could work independent if:

  1. records created by aggregate excluded from recorder purge process - can be solved at recorder configuration level (or probably a warning in the component documentation)

  2. and aggregation period does not exceed amount of purge days of the recorder

Second constraint can be solved like:

  1. minimal purge days = 1, then this constraint does not affect period daily, hourly.
  2. by default purge days = 10, then we can check if weekly data exists for the sensor. If not see 3.
  3. If recorder purge days < sensor aggregation period - perform aggregation of the maximum possible period (i.e. daily or weekly) and record aggregation for required period based on aggregation from smaller period (i.e. weekly aggregated value calculated from 7 daily aggregates).

This isolated approach looks cleaner to me. Requires only recorder.purge_keep_days value from configuration.

Please like this comment if you agree with idea to create new aggregate component instead of modifications in utility_meeter, recorder or statistics component.

Let me call your attention to Collect long-term statistics · Discussion #559 · home-assistant/architecture · GitHub

1 Like

Thank you for pointing me out to this discussion.
I also found your PoC - Storing your very long history - RRD Recorder and component GitHub - dgomes/rrd_recorder: RRD Custom Component for Home Assistant.

Why you decided to use external dependency RRDtool - About RRDtool instead of implementation of aggregation logic with SQLs?

I’m a past user and contributor to RRDtool, and I think we shouldn’t reinvent the wheel :slight_smile:

I made a related Feature Request to update Recorder:

It didn’t get much attention, even though this topic keeps coming up in different forms. Bottom line is, Recorder is a pretty blunt instrument, and quite primitive compared to other systems. The defaults are horrible, even downright risky. It sets new users up for failure. There are a lot of ways it could be improved and simplified.

Obviously I’d like to see more votes for my request, above. But the bottom line is Recorder needs some serious attention.

Indeed recorder retention by entity is important to reduce database. The feature request does not contradict current RFC with agregation +1 vote from me.