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

Feature Requests
1

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.

2

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
3

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.

4

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:

1 Like
5

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.

6

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

1 Like
7

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?

8

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

9

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.

10

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

11

Huh, 16 votes already… I had a short moment today to look in to the issue and made a quick PoC to filter out data for the utility meetered sensors report:

Create history_recorder.py:

logger.debug("Recording for history...")

targets = data.get("targets", None)
logger.debug("targets: " + str(targets))

suffix = data.get("suffix", "_history")
logger.debug("suffix: " + str(suffix))

if not targets is None:
    for target in targets:
        entity = hass.states.get(str(target))
        id = str(target) + str(suffix)
        logger.warning("For " + id + " state: " + str(entity))
        if not entity is None:
            hass.states.set(id, float(entity.state), entity.attributes, False)
        else:
            logger.error(str(target) + " does not exsists!")
            
else:
    logger.error("No target entities to process!")

Add in to services.yaml (if not exist then create):

history_recorder:
  description: Write sensor value in to *_archive value. Add to HASS recorder cleaner rule to ignore this values.
  fields:
    targets:
      description: Comma separated list of sensors.
      example: sensor.sun_panel_temperature,sensor.boiler_teperature
    suffix:
      description: Suffix to add to the sensor name when making snapshot, e.g. end of day or end of month.
      example: _eod

Then you can create automations to copy values at exact time in to historical sensor value, for example:

  - alias: History of monthly values
    trigger:
      - platform: time
        at: "23:59:00"
    condition:
      - condition: template
        value_template: "{{ states('sensor.is_end_of_month') == 'True' }}"
    action:
      - service: python_script.history_recorder
        data:
          suffix: _eom
          targets:
            - sensor.boiler_monthly_energy
            - sensor.boiler_monthly_energy_cost
    mode: single
    id: hJ5zebYHMwKbbaGFVTQfTaDMrYjWvhSY

The suffix “_eom” will be added to the original sensor name.

Finally in configuration.yaml exclude historical values by suffix filter:
recorder:
purge_keep_days: 2
exclude:
entity_globs:
#see history_recorder.py
- sensor._eod
- sensor._eom

Tested on Apex charts card and I can get daily and monthly values in to the chart.

type: custom:apexcharts-card
update_interval: 1d
graph_span: 30d
header:
  show: false
  show_states: true
  colorize_states: true
all_series_config:
  stroke_width: 2
  show:
    legend_value: false
series:
  - entity: sensor.boiler_monthly_energy_eod
  - entity: sensor.boiler_monthly_energy_cost_eod

To show dashboard with values per month send in to prefix name of the month + “_eom”. Then in the UI create
type: entities
entities:
- entity: sensor.boiler_monthly_energy_jan_eom
…
- entity: sensor.boiler_monthly_energy_dec_eom

Hope this will help for someone. I will update if any further progress will happen.

Updated 16 Aug 2021: By default its not possible to skip records from cleanup by recorder. But Apex Charts has a cache and can keep showing charts even the data does not exists in the DB. Therefore you can see monthly report in ApexChart card even there is data only for 2-3 days in the database. Offcourse you have to open this card at least once during the cleanup period to cache the data. Not sure how long the hack will work. See below link to another CR how to skip specific entities from cleanup by the Recorder.

12

Thank you for the instruction. However, if I exclude those _eod _eom etc. values in recorder, my apexcharts-card shows nothing on those entities. Could you please point me some directions?

13

Yes I had empty charts in the beginning or (for some reason) if automation failed to record sensor values at midnight.

Values recorded at midnight by python script when automation is executed. To debug - either you run automation immediately to get *_eod recorded in to the database or wait until next day.
Recorder exclude - is just instruction not to delete values from DB.

14

I did call the service in the developer tool to be sure that _eod entity has data. It could be confirmed that an Entity card shows correctly (latest) value. But as far as I understand, apex charts card does need historical data of an entity to properly generate data to be shown. That’s why I’m puzzled by the Recorder exclude part. Reading that last sentence of your reply, I think you meant include, instead of exclude, to “not to delete values from DB”?

15

You right, I miss understand how recorder works.

Check this - Support for multiple recorder components - #12 by KiLLeRRat

Looks like recorder needs some hands on it to skip *_eod and *_eom from cleanup.