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