right…
sidenote: I did manage to fix it in the end. I initially did not expect the cost to be calculated by sensor at all, I just expected the cost to be a fixed value per intereval and then just be multiplied by the usage, to be easily presented. But now I know that the total cost (sum
) is infinite by sensor, and then again calculated when presented in the dashboard (end of period - start of period, which can be hours/days/weeks/months). Assumption is mother to all *beep*
-ups…
For completeness and Google Search, I managed to leverage the following to fix it for myself.
## Open the database
sqlite3 /some/path/config/home-assistant_v2.db
## Set some things
sqlite> .header on
sqlite> .mode column
sqlite> .width 3 40 10 20 8 7 50
### Retrieve meta data
sqlite> SELECT * FROM statistics_meta;
id statistic_id source unit_of_measurement has_mean has_sum name
--- ---------------------------------------- ---------- -------------------- -------- ------- --------------------------------------------------
1 sensor.power_consumption recorder W 1 0
2 sensor.power_production recorder W 1 0
3 sensor.energy_consumption_tarif_1 recorder kWh 0 1
4 sensor.energy_consumption_tarif_2 recorder kWh 0 1
5 sensor.energy_production_tarif_1 recorder kWh 0 1
6 sensor.energy_production_tarif_2 recorder kWh 0 1
7 sensor.gas_consumption recorder m³ 0 1
8 sensor.energy_consumption_tarif_2_cost recorder EUR 0 1
9 sensor.energy_consumption_tarif_1_cost recorder EUR 0 1
10 sensor.gas_consumption_cost recorder EUR 0 1
11 sensor.envoy_current_power_production recorder W 1 0
[[omitted]]
24 sensor.energy_production_tarif_1_compens recorder EUR 0 1
25 sensor.energy_production_tarif_2_compens recorder EUR 0 1
so I deducted that the metadata_id
for me is 8
and 10
.
I then checked the statistics.
sqlite> SELECT * FROM statistics WHERE start LIKE "2021-12-02%" AND ( metadata_id = 8 OR metadata_id =10 ) LIMIT 80 ;
id created start mean min max last_reset state sum metadata_id
--- ---------------------------------------- ---------- -------------------- -------- ------- -------------------------------------------------- --------------- --------------- -----------
[[omitted]]
163 2021-12-02 10:00:10.444939 2021-12-02 2021-11-10 13:38:19.224694 22.036310880004 27.090024240004 8
164 2021-12-02 11:00:10.989844 2021-12-02 2021-12-02 10:19:24.428180 0.0709383600000 226.71689052004 8
[[omitted]]
163 2021-12-02 10:00:10.445603 2021-12-02 2021-11-10 13:38:19.225395 105.67769912000 136.46243408000 10
164 2021-12-02 11:00:11.088786 2021-12-02 2021-12-02 10:19:24.429366 0.2594843999999 243.38401080000 10
[[omitted]]
So now I could clearly see where it’d gone wrong. After this I updated the cost using the following query:
UPDATE `statistics`
SET sum = sum - 199.62686628
WHERE metadata_id = 08 AND sum > 226;
where 199.62686628
is 226.71689052004
minus 27.090024240004
, effectively resetting the cost for that specific hour to 0 EUR, which is closer to the truth than 200 EUR.
I then did the same for the gas:
UPDATE `statistics`
SET sum = sum - 106.92157672
WHERE metadata_id = 10 AND sum > 243;
where 106.92157672
is 243.38401080000
minus 136.46243408000
.
I hope this will help someone else in the future!
@burkemw3 thanks again for the pointers, they did help out
Kind regards,
Jimmy
Edit: Meh, this change migrated the issue to today… I did a pull on a new docker image, deleted and restarted the container and there it was… So, I had to do it again, but now for today
Due to probably the sums continuing in the table statistics_short_term
, the issue persisted in the historical data.
I checked the issue for today:
sqlite> SELECT * FROM statistics WHERE start LIKE "2022-01-31%" AND ( metadata_id = 8 OR metadata_id =10 ) LIMIT 80 ;
id created start mean min max last_reset state sum metadata_id
--- ---------------------------------------- ---------- -------------------- -------- ------- -------------------------------------------------- ---------------- ---------------- -----------
[[omitted]]
523 2022-01-31 12:00:10.450736 2022-01-31 2022-01-13 11:12:24.607388 18.1986158400012 89.5487919600548 8
523 2022-01-31 13:00:10.626738 2022-01-31 2022-01-13 11:12:24.607388 18.2648718000013 289.241914200055 8
[[omitted]]
523 2022-01-31 12:00:10.451365 2022-01-31 2022-01-13 11:12:24.608118 126.45210256 501.552042320003 10
523 2022-01-31 13:00:10.627391 2022-01-31 2022-01-13 11:12:24.608118 126.92823584 608.949752320003 10
But the series in the statistics_short_term
table looked fine. No jumps.
So what I did was take the difference in the jump in the historical data, and apply it to both longterm and shortterm data.
UPDATE `statistics`
SET sum = sum - 107.39771
WHERE metadata_id = 10 AND sum > 600;
UPDATE `statistics_short_term`
SET sum = sum - 107.39771
WHERE metadata_id = 10;
UPDATE `statistics`
SET sum = sum - 199.69312224
WHERE metadata_id = 08 AND sum > 280;
UPDATE `statistics_short_term`
SET sum = sum - 199.69312224
WHERE metadata_id = 08;
And now all is well again! Hoorah!
What I should have done initially was run the query with 199.62686628
and 106.92157672
on the statistics_short_term
table as well.