Negative Values in Energy Dashboard since last update

Configuration
81

Apologies, should have clarified a bit better.

Koelvriezer is actually a refrigerator which has a pretty stable consumption (between 20- 30 kWh per month). I saw in the energy dashboard that in the month of may the value was negative and that the value in June was positive.

When I ran the 2.2.0 script (on the unaltered database) this morning the result was that the value of koelvriezer in May appeared correct, but the value in June became zero. As the month of June is almost over I would expect a value of around 20-30kWh to be shown.

82

Hi @marcelhoogantink and @fversteegen

Thanks for the hint @marcelhoogantink !
It seems like that I introduced a bug with the last release with the feature of searching the first valid value.
I fixed it with the latest release 2.2.1. Can you both provide the results on your side?

1 Like
83

Hi @Bl4DEx,

I did a first run with 2.2.1 and a graphic view of some results and it looks all OK.
(i will look into it deeper tomorrow in the database results)

84

Tested this morning and it looks as everything is as expected: proper graphs with expected results :smiley:

Will let it sit for a day to see if there are no unexpected side results.

EDIT: I am getting negative values after a few minutes. I think this is caused by myself as I did not bother to properly restore the .bak files. Am now rolling back a backup which predates the whole journey of correcting the database and check again.

85

So I digged deeper today, and I am sorry there are two issues, I think:

  1. Like @fversteegen I also got negative values in my energy list of separate devices. But … I think I know the cause of it:
    In my case this specific Riemann integration of this device is setup in Wh (all others in kWh) and the statistics are in kWh. (HA does some automatic recalculation for Wh and kWh, I think)
    So the value of lastValidState = recalculateStatistics(...) will be in kWh.
    This value is sent to fixLastValidState_Riemann which stores the value in file core.restore_state which needs to be in Wh. So there is a factor 1000 which in the end is causing the negative values.

  2. I my opinion, the latest short-time statistic-value, the latest long-time statistic-value and the latest state-value of an (energy)-device should be equal, taking into account that the statistic values always are a little ‘behind’. But every whole hour they should (almost) match.
    After the repair, the last value of the State of the device is much to low, caused by the resets of the Riemann-bug. So the changed states seems not correct to me, Am I right here?

86

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 :slight_smile:

87

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.

2 Likes
88

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
}
89

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 …

:fireworks: :fireworks:NO NEGATIVE VALUES !!! :fireworks: :fireworks:

  1. 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.
1 Like
90

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"
    },
92

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!

93

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
94

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

95

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!

97

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

98

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 :slightly_frowning_face:

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

1 Like
99

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?

1 Like
100

in my case, this fixed my dashboard issue :slight_smile:

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

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!

102

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

image
image1063×610 39.4 KB