Negative Values in Energy Dashboard since last update

[fversteegen (Frank)]

Would point 1 then be fixed by manually editing core.restore_state and multiplying the values by a 1000? As the purpose of this script is to correct issues once I am more than happy to do that manually

[karwosts]

It’s really not necessary to export anything to excel to fix this, it’s easy to just run a single SQL command that increases sum by a fixed value on multiple rows, e.g.:

UPDATE "statistics"
SET sum = sum + 725.243
WHERE "metadata_id"
IS "279"
AND
id >= 1382082

Here’s my full GH post about how I fixed this purely with SQLite addon and 3 SQL queries.

Cannot fix invalid energy dashboard data

[marcelhoogantink]

First check core.config_entities file for Riemann by searching for [ "domain": "integration" ] and check the lines unit_prefix. The ones with [ "unix_prefix" : "none" ] are those witch shout be multiplied.

{
        "entry_id": "abc292e3f1aec021a2428871309eef25",
        "version": 1,
        "domain": "integration",
        "title": "Energie vaatwasser (7)",
        "data": {},
        "options": {
          "name": "Energie vaatwasser (7)",
          "source": "sensor.id_7_19",
          "method": "left",
          "round": 2.0,
          "unit_prefix": "none",
          "unit_time": "h"
        },
        "pref_disable_new_entities": false,
        "pref_disable_polling": false,
        "source": "user",
        "unique_id": null,
        "disabled_by": null
}

[marcelhoogantink]

Here my further test-results and findings:

1. I multiplied the values for my ((Wh) Riemann-entity with 1000 in the file core.restore.state after running the fix and before restart the HA-core.
   (Watch out: this value is given three times in these kind of entities, multiply all three!!):

{
      "state": {
        "entity_id": "sensor.energie_vaatwasser_7",
        "state": "460201.82",
        "attributes": {
          "state_class": "total",
          "source": "sensor.id_7_19",
          "unit_of_measurement": "Wh",
          "device_class": "energy",
          "friendly_name": "Energie vaatwasser (7)"
        },
        "last_changed": "2023-06-30T17:43:50.594136+00:00",
        "last_updated": "2023-06-30T17:43:50.594136+00:00",
        "context": {
          "id": "01H46PEWJ22B511DA6E34HYYFD",
          "parent_id": null,
          "user_id": null
        }
      },
      "extra_data": {
        "native_value": {
          "__type": "<class 'decimal.Decimal'>",
          "decimal_str": "460201.82"
        },
        "native_unit_of_measurement": "Wh",
        "source_entity": "sensor.id_7_19",
        "last_valid_state": "460201.82"
      },
      "last_seen": "2023-06-30T17:44:04.827239+00:00"
    }

Now waiting for passing a full hour to see whether the negative energy bars are gone for the individual devices in the Energy-Dashboard.

[EDIT} passing full hour 20:00 and …

NO NEGATIVE VALUES !!!

2. Like the short time statistics, the states will be all corrected within ~ 10 days. Old values will fade away. So no extra changed made for these.

[fversteegen (Frank)]

@marcelhoogantink

ative_unit_of_measureme

Had a look at my core.restore_state and cannot find ant Wh sensors in it. All he misbehaving sensors find the following structure, so I guess my problem is different from yours…

    {
      "state": {
        "entity_id": "sensor.integral_oven",
        "state": "9.04",
        "attributes": {
          "state_class": "total",
          "source": "sensor.oven_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "friendly_name": "Oven energy"
        },
        "last_changed": "2023-07-01T16:47:39.305491+00:00",
        "last_updated": "2023-07-01T16:47:39.305491+00:00",
        "context": {
          "id": "01H495MQ992BY2BWPF39CS1YX6",
          "parent_id": null,
          "user_id": null
        }
      },
      "extra_data": {
        "native_value": {
          "__type": "<class 'decimal.Decimal'>",
          "decimal_str": "9.04"
        },
        "native_unit_of_measurement": "kWh",
        "source_entity": "sensor.oven_power",
        "last_valid_state": "9.035963638705555555282497888"
      },
      "last_seen": "2023-07-02T05:59:38.697755+00:00"
    },

[marcelhoogantink]

Oops @fversteegen , mistake from my side: The file you have to check is core.config_entries. In this file the Riemann Integrations can be found.
Sorry!

[fversteegen (Frank)]

I have searched both core.config_entries and core.restore_state, but neither of them has got any sensor which is in Wh. Even put a stab at core.entity_registry. See below relevant extracts for problematic sensor sensor.integral_wasmachine

core.entity_registry

         "config_entry_id": null,
        "device_class": null,
        "device_id": null,
        "disabled_by": null,
        "entity_category": null,
        "entity_id": "sensor.integral_wasmachine",
        "hidden_by": null,
        "icon": null,
        "id": "fc3015e4a4c3e5ee3473b8116616ce8d",
        "has_entity_name": false,
        "name": "Wasmachine energy",
        "options": {
          "sensor": {
            "display_precision": null
          },
          "conversation": {
            "should_expose": false
          },
          "cloud.google_assistant": {
            "should_expose": false
          },
          "cloud.alexa": {
            "should_expose": false
          }
        },
        "original_device_class": null,
        "original_icon": "mdi:chart-histogram",
        "original_name": "integral_wasmachine",
        "platform": "integration",
        "supported_features": 0,
        "translation_key": null,
        "unique_id": "1bb42230-96d0-4b07-8494-a734c6750056",
        "unit_of_measurement": null
      },

core.config_entries

NOTHING

core.restore_state

    {
      "state": {
        "entity_id": "sensor.integral_wasmachine",
        "state": "17.62",
        "attributes": {
          "state_class": "total",
          "source": "sensor.wasmachine_power",
          "unit_of_measurement": "kWh",
          "device_class": "energy",
          "friendly_name": "Wasmachine energy"
        },
        "last_changed": "2023-07-08T06:53:53.654155+00:00",
        "last_updated": "2023-07-08T06:54:10.381885+00:00",
        "context": {
          "id": "01H4T4F1YD7A23ADR64606M9DT",
          "parent_id": null,
          "user_id": null
        }
      },

And the source of the sensor from configuration.yaml

  - platform: integration
    source: sensor.wasmachine_power
    unique_id: 1bb42230-96d0-4b07-8494-a734c6750056
    name: integral_wasmachine
    unit_prefix: k
    round: 2
    method: left

[Prime69 (Vian)]

Hi @Bl4DEx , I just come back from holiday and noticed the issue on my HA happened again and want to fix it now using your script. I am not using HA in container but using a HASSIO installation on a RPI. does the script work also on this type of installation?
Thank you

[joesalty77]

Hi All! I am running Home Assistant Operating System in virtual environment, under Virtualbox. I’m not so familiar with Docker, and other stuffs, but I would like know that is it possible, somehow, to run this python script in HA OS? Thank you!

[Bl4DEx (Sebastian Hollas)]

Hi all,

might be a bit little late but the script can be execute on any HomeAssistant Database. You just need to know where the database is located. I don’t think that it matters how you run HomeAssistant. Every HA instance requires a database to work. The script fixes a database either directly as a file or connected via a SQL Server.
It’s up to you to find out where your instance has it’s database located

[hollie (Lieven Hollevoet)]

Hey @Bl4DEx ,

just to let you know: I hit this issue on HA 2023.7.2 when my PV inverter was offline for 2 weeks. When it became available again I got an invalid -5MWh entry on the energy dashboard

I ran your script and it fixed the issue flawlessy. Thanks a lot for taking the time to write this script and to share it with us.

Best regards,
Lieven

[ints (Ints Purmalis)]

Hi,

Is there some video tutorial how to use this script? i’m updated from 2023.5 to 2023.9 and got negative values. I’m not familiar with doing script in HA.
i manage to cllone this repository with Terminal&SSH, but step 2 is not working, when run command.
Can someone help me?

[cicciuzzo]

in my case, this fixed my dashboard issue

UPDATE "statistics"
SET sum = sum + 5167.127
WHERE "metadata_id"
IS "97"
AND
id >= 1719880

[fversteegen (Frank)]

I also finally managed to fix the issue through the developer tools → statistics tool. Whenever I could find the culprit (large negative value) I changed that. In the case where I could not fin the culprit I changed 1 of the values of that day with an adjustment equal to the negative value in the energy dashboard (eg -100 would become an adjustment of +100 during that day)

I did not have to run this or any other script to achieve the results I wanted. Still many thanks to all and especially @Bl4DEx for their work on the scripts which hugely increased my understanding on how the underlying database works!

[jcd420]

I tried changing the values but it makes no difference.

  - platform: group
    name: vpwSuit1kw
    unique_id: sensor.vpwsuit1kw
    type: sum
    ignore_non_numeric: true
    unit_of_measurement: kWh
    device_class: energy
    state_class: total
    entities:
      - sensor.pwmoniterlightkw
      - sensor.pwsuit1dimmerkw
      - sensor.pwsuit1nightstandkw
      - sensor.pwsuit1bathdimmerkw
      - sensor.computersw_energy

[HerrRausB]

Thanks a lot - that saved my day (so far)

[e-raser]

To summarize two questions:

1. This was introduced with HA Core 2023.5 and only affected Riemann Sum integration. Right?

2. Which HA Core release fixed this issue? I went through all posts but could not find a PR for the fix.

---

I think I might have run into the same issue once updating to HA Core 2023.4.6 back in November. Since then I see this regularly and only discovered today…

Energy dashboard screwed due to decreasing input sensor - how to a) fix and b) prevent in future?

I took a look in the history, using statistics card set to 365 days with hour data. And there are more incidents of that type, as already shown with: https://community-assets.home-assistant.io/optimized/4X/6/1/2/61248e82709c11a64f6201fe3bbcc82375807d9d_2_1380x286.png But today is not the first incident/time, it had an actual impact on the LTS and energy dashboard data. Here filtered for 180 days to have a better scale: [grafik] The first incident was on 2023-11-13. There were much more since…

Edit:
I made some more research in my InfluxDB and I can confirm updating HA Core to 2023.4.6 definitely introduced this issue (I updated on 2023-11-04). My utility meters used are unavailable from time to time, causing these issues to sometimes show up in the energy dashboard.

Question 2 please! Which HA Core release fixed this issue?

Thank you.

[Edwin_D]

Please note that this is often caused by sensors that return 0 when the sensor should have reported unavailable, for instance during a reboot. This can be due to a flaw by the integration builder, but often it is caused by template sensors added by us users that are missing an availability template. My problems were fixed when I added the availability template to my misbehaving water sensor.

See also this discussion:

Energy dashboard screwed due to decreasing input sensor - how to a) fix and b) prevent in future?

The fact that you didn’t have this problem without availability template is luck. Also the behavior change could be from the integration that provides the three subsensors. I must admit I too had more problems like these from my water sensor around the same time. They also lacked an availability template, so indeed something changed in HA for the issue to become more apparent. I suspect the statistics start working earlier during the boot process, when not all integrations are done loading. B…

[e-raser]

Likely a good thing to work with availability_template, which is what I did yesterday.

---

raw input sensor → utility meter sensor → template sensor

---

BUT as the input sensor for my affected template sensors are from a utility meter…

• that integration should know about how to handle unavailable its own input sensors the right way
• still something has changed in HA!

Before (in my case) HA Core 2023.4.x there was no need to suppress unavailable states, this was handled bullet-broof by HA internally. The fact so many (at least those who care about their data or accidentally stumble over broken energy dashboards) suffer from this proves this is not an individual issue.

Therefore while monitoring if there are more incidents after I added the availability_templates I urgently want to know if and which HA Core version fixed this definitely with an update introduced behavior.

[Edwin_D]

@e-raser

Before (in my case) HA Core 2023.4.x there was no need to suppress unavailable states, this was handled bullet-broof by HA internally.

You’re still not getting it. You yourself are putting | float(0) in your template, saying to use 0 if the sensor is unavailable - instead of making the summation sensor itself unavailable.

In doing you created a sensor that goes up and down instead of just up. And because your summation sensor is never unavailable, even if you miss a value, is exactly why HA cannot do a single thing about it and will just do what the state_class says it should do when the value decreases. There’s no bullet proofing for that.

The only thing that happened since a few months is that your sensors are more often seen unavailable, most likely during a reboot. During that time it is quite normal that sensors are not available. So it is not a bug, it is expected behaviour happening more often than it used to. Even if this is reverted, you will still encounter the problem when sensors become unavailable and you do not have the availability template.

Putting utility meter inbetween does not solve anything, it may even make it worse.