Energy cost calculation off for 1 single day

Hi all,

I’ve been running HA since last November, in docker, on a Pi:

NAMES           IMAGE                                                      STATUS
adguardhome     adguard/adguardhome:v0.107.0-b.11                          Up 4 days
homeassistant   ghcr.io/home-assistant/raspberrypi4-homeassistant:stable   Up 4 days
unifi           jacobalberty/unifi:v6                                      Up 4 days (healthy)

The command/parameters used:

IP=$(ip -4 addr show eth0 | grep -oP '(?<=inet\s)\d+(\.\d+){3}')
DOCKERTAG='stable'

docker run -d \
  --name homeassistant \
  --privileged \
  --restart=unless-stopped \
  -e TZ='Europe/Amsterdam' \
  -p ${IP}:8123:8123 \
  -v /some/path/config:/config \
  --network=bridge \
  ghcr.io/home-assistant/raspberrypi4-homeassistant:${DOCKERTAG}

Everything is working nicely, the Energy Dashboard is cool. I run HA with a “Smartgateway” hooked up to my Smart Meter P1 port, which I initially put in the configuration.yaml

sensor:
  - platform: dsmr
    host: 192.168.100.202
    port: 23
    dsmr_version: 5

It all works nicely, except for the fact that 1 single day (roughly a month after starting, so about a month ago or something) has a wrong cost calculation. The usage (Kwh/m3) seems correct, but for some reason it does not adhere to my defined tarif, but uses something else (roughly a factor 20 higher). I’ve set the tarifs as a static price at the beginning and not touched it since (fixed price 1 year contract since July).

Any regular day:

That one particular day:

I’d be very happy if I can fix this somewhere, any help would be appreciated!

Kind regards!

This fall, there was a bug with energy and the daylight saving time switch that caused issues with kwh reporting. I had to manually edit some tables in the sql database, I think the tables were statistics and statistics_short_term. The tables contain a running total of the value, so I had to modify all the rows for data points after the problem timestamp.

I think maybe Issue after DST time change [update to 2021.10.7+ or 2021.11.0b4+ recommended] · Issue #58783 · home-assistant/core · GitHub was the bug, but I’m not sure.

I also haven’t found any of the commands nor other threads I referenced, but there were a number of questions about it this past fall.

Cheers Matt, I’ll have a look :slight_smile:

Thing is, with me the kwh is fine, it’s just that for that one day the Tarif is 31 EUR/kwh (201 EUR for 6,5 kwh consumed on Thursday december 2nd), and the day prior and past, it’s “back to normal” at 0,23 EUR/kwh (1,37 EUR for 5,9 kwh consumed on Wednesday december 1st, and 1,63 EUR for 7 kwh consumed on Friday december 3rd).

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

@burkemw3 thanks again for the pointers, they did help out :+1:

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

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.

1 Like

I have accidentally put in $30 instead of 30c for energy prices… changed it but it doesnt calculate across old data… which I guess is good if power prices change. Im loathe to go into sql to modify anything. Is there anywhere to kill all the old $ energy prices. or am I going to have this exxtra $200 for a year

hi!

I’m afraid it’s either dabbling in the database (updating the sum of the costs is basically the only option, as deleting the entries with the incorrect values will just shift the problem to a different timestamp) or just deleting everything and starting from scratch?