How recalculate values in 'statistics' table

Configuration
1

For my energy dashboard, I read the SOLAR power from my SMA, this works, but sometimes, it gives me 0 back as a value for ‘pv_gen_meter’
Due to this, I get strange calculated values inside my ‘statistics’ table.

image
image935×473 10.6 KB

As you can see, the SUM columns suddenly jump from the correct value to 7774.336 after the STATEwas ‘0’ for a few moments. This results in a completely wrong view in my energy dashboard :
image
Can I fix this somehow? Is it possible to recalculate the 'SUM column completely, after I fill in the missing STATE values ?

I open the SQLite DB over a samba share on my laptop, because when I do this with the SQLite plugin on my RPi3, I get ‘bad gateway’ errors.

Erwin

2

Prevention: https://community.home-assistant.io/t/how-to-prevent-entity-from-resetting-to-0/369363/2?u=tom_l

1 Like
3

Following. I imported values from last year and I recalculated the sum values with a script but HA keeps using the old sum values for new entries

4
1 Like
5

I found out by myself, thanks.
I recalculated the sum values in the short term statistics as well.
Now the sums are calculated correctly in the long term statistics table with the correct values.
Still some big spikes in the values that I can’t explain.
I even checked the values with excel using a ODBC connection to the database.

Schermafbeelding 2022-01-08 185251
Schermafbeelding 2022-01-08 1852511956×936 41.3 KB

Funny thing is that most problems appear to exist in my solar production numbers.
My other energy sensors which I also updated with figures from last year appear to be fine.

6

Mag ik vragen hie je deze herberekend hebt ?

7

Ik heb een PHP script gemaakt die controleert of het verschil tussen de som van 2 records gelijk is aan het verschil in de states van diezelfde records. Als het verschil niet gelijk is dan wordt de som aangepast.

Het is misschien niet het meest efficiënte script, je moet het net zo vaak uitvoeren (F5) tot er geen output meer op het scherm komt. En voor de short term statistics heb ik eenzelfde script maar dan met de andere tabel. En je moet even kijken wat de metadata id’s zijn voor jouw energie meters.

<?php
include "db_connect.inc";

$metadata_id = 50;
$afronden = 3;

$query = "SELECT *
          FROM statistics
          WHERE metadata_id = $metadata_id and (created like '2021-%' or created like '2022-%')
          ORDER BY created;";

$result = $mysqli->query($query) or die ("FOUT: " . $mysqli->error);
$aantal = $result->num_rows;

$teller = 0;
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
  $teller++;
  $id[$teller] = $row['id'];
  $state[$teller] = round($row['state'],$afronden);
  $sum[$teller] = round($row['sum'],$afronden);

  if ($teller > 1) {
    $sumsoll = $state[$teller] - $state[$teller-1] + $sum[$teller-1];
    $sumsoll = round($sumsoll,$afronden);
  } else {
    $sumsoll = $sum[$teller];
  }
  $diff = round($sumsoll - $sum[$teller],$afronden);

  if ($diff != 0) {
    print "$id[$teller] &nbsp; $row[created] &nbsp; $row[state] &nbsp; $row[sum]";
    print "&nbsp; $sumsoll &nbsp;";
    print "$diff&nbsp;";
    print "verschil geconstateerd!<BR>";

    $updatequery = "UPDATE statistics
                    SET sum = $sumsoll
                    WHERE id = $id[$teller];";
    print "<BR>$updatequery<BR>";
    $mysqli->query($updatequery) or die ("FOUT: " . $mysqli->error . "<BR>$updatequery<BR>");
  }
}
?>
1 Like