Utility meter should only write one a cycle

Configuration
1

Hello,
I have daily and monthly utility meters. Data is saved into influxdb as well.
I would expect the daily meter to accumulate the energy value and only save the value to the database once a day, and the monthly one to only save the value once a month.
However, the utility meter value is updated constantly, more than once a day, or once a month. This increases disk space usage in influxdb and home assistant for no reason.
Anyone can let me know if this is the proper behaviour and if there is any fix for it?
Thanks in advance
Here is my configuration:

# Utility meters
utility_meter:
  apartment_1_montly_energy_kwh:
    source: sensor.apartment_1_energy_kwh
    cycle: monthly
  apartment_1_daily_energy_kwh:
    source: sensor.apartment_1_energy_kwh
    cycle: daily

sensor:
  - platform: mqtt
    name: "Apartment 1 power"
    state_topic: "panel/sensor/apartment_1_power/state"
    availability_topic: "panel/status"
    unit_of_measurement: "W"  
  - platform: mqtt
    name: "Apartment 1 energy"
    state_topic: "panel/sensor/apartment_1_energy/state"
    availability_topic: "panel/status"
    unit_of_measurement: "Wh"  
  - platform: mqtt
    name: "Apartment 1 power factor"
    state_topic: "panel/sensor/apartment_1_power_factor/state"
    availability_topic: "panel/status"
    unit_of_measurement: ""

I also have Template sensors to convert the energy value from sensor to kWh:

- platform: template
    sensors:
      apartment_1_energy_kwh:
        entity_id: sensor.apartment_1_energy
        value_template: "{{ states('sensor.apartment_1_energy') | float / 1000 }}"
        friendly_name: "Apartment 1 energy kWh"
        unit_of_measurement: kWh

As you can see below, the utility meter is saving lots of records to grafana. I am only interested in the value at the end of the day (for faily) and at the end of the month (for monthly):

image
image2834Ă—1266 304 KB

1 Like
2

Nope.

It accumulates for the cycle period then resets to zero at the end of the period.

There’s a reason. Half way through a month I want to be able to see the value accumulated so far. I don’t want to have to wait until the end of the month for a value.

If you only want one value stored at the end of the cycle, use a template sensor or automation to record the utility meter just before the end of the cycle.

3

Thanks for the info,
I understand it can be useful for some users.
Someone on Github has suggested to dowsample data in influxDB in order to only keep the max value for each day.
I will look into that and post it here in case someone else is in my situation
Regards

4

Hi,
did you manage to acheive that? If yes, can you share here your yaml files?
What I want to do is really closed I think.

5

I wanted to integrate exactly this behaviour.
Do you have the code ready for that? I am motivated to try, but i probably going to end up with very inelegant code…

6

I’m struggling with bad internet connectivity at the moment I think you should be able to find what you want here:

https://community.home-assistant.io/t/daily-energy-monitoring/143436/

1 Like
7

I created a script with pyscript integration to do a daily cleanup of all intermediate values of my utility meters.

I am using postgres as a database and use following queries to achieve this. The queries each scan for the max value of the cycle and deletes the rest.

# Utility meter with cycle 5mins
DELETE FROM states where entity_id = 'sensor.energy_mains_usage_5mins_peak' and state_id not in (with a as (select *, ROW_NUMBER() OVER (PARTITION BY date_trunc('hour', created) + date_part('minute', created)::int / 5 * interval '5 min' ORDER BY state desc) as rn from states where entity_id = 'sensor.energy_mains_usage_5mins_peak') select state_id from a where rn = 1);

# Utility meter with cycle 1hour
DELETE FROM states where entity_id = 'sensor.energy_mains_usage_hourly_peak' and state_id not in (with a as (select *, ROW_NUMBER() OVER (PARTITION BY date_trunc('hour', created) ORDER BY state desc) as rn from states where entity_id = 'sensor.energy_mains_usage_hourly_peak') select state_id from a where rn = 1);

# Utility meter with cycle 1day
DELETE FROM states where entity_id = 'sensor.energy_mains_usage_daily_peak' and state_id not in (with a as (select *, ROW_NUMBER() OVER (PARTITION BY date_trunc('day', created) ORDER BY state desc) as rn from states where entity_id = 'sensor.energy_mains_usage_daily_peak') select state_id from a where rn = 1);

# Utility meter with cycle 1month
DELETE FROM states where entity_id = 'sensor.energy_mains_usage_monthly_peak' and state_id not in (with a as (select *, ROW_NUMBER() OVER (PARTITION BY date_trunc('month', created) ORDER BY state desc) as rn from states where entity_id = 'sensor.energy_mains_usage_monthly_peak') select state_id from a where rn = 1);

# Utility meter with cycle 1year
DELETE FROM states where entity_id = 'sensor.energy_mains_usage_yearly_peak' and state_id not in (with a as (select *, ROW_NUMBER() OVER (PARTITION BY date_trunc('year', created) ORDER BY state desc) as rn from states where entity_id = 'sensor.energy_mains_usage_yearly_peak') select state_id from a where rn = 1);
8

Thanks for sharing this Sven! I think utility_meter is great but I probably haven’t read enough about the behaviour before using it.

There’s a reason. Half way through a month I want to be able to see the value accumulated so far. I don’t want to have to wait until the end of the month for a value.

Doesn’t this give you duplicate records for:

  • cycle: hour
  • cycle: day
  • cycle: week
  • cycle: month
  • cycle: year
    These entities reset at their given intervals but still popuplate the database:

RESULT: 10 days = around 120,000 records in the states table.
EXPECTED: 240 (hours) + 10 (days) + 2 (weeks) = 252.

The reason I started using utility_meter is that it would reduce the crazy amount of db records :slight_smile:
(with 12 records for the monthly usage, 52 for the weekly usage, etc.) Should have done a better job investigating :slight_smile:

If you only want one value stored at the end of the cycle, use a template sensor or automation to record the utility meter just before the end of the cycle.

It looks like the only solution is to create a few hundred template sensors (not just for utility_meter) with the right behaviour and exclude all “real” sensors. Ouch…