A newbie experience with the Energy dashboard / sqlite-web

Energy
1

No question, just a story with maybe the solution for your similar problem.

I just want to share my newbie experience with the energy dashboard; in this dashboard I added my water usage. The data comes from a Arduino UNO with sensor LJ12A3-4-Z/BY-5V combination running a home brewed sketch only sending data on actual usage. All further logic is dealt inside HA. Missing a sensor with the total usage inside HA (collected this in a input.number) I let this total bounce over MQTT to have this total in a sensor field (sorry, at that point in time this was my solution to solve this problem :smiley:)

After a month running I notice a mistake in my total (due to an incorrect start reading) I corrected this but screwed up my statistics. So, at this point in time I wanted to look under the hood of statistics. Running HA inside a docker I needed to install sqlite-web myself, thus had to built my first image ever (did this off course flawlessly in 2 seconds but the truth is I had some wrong approaches and hick ups to conquer).

With sqlite-web up and running and help from some topics all over the place I learned the initial basics of SQL and located my problem:

SELECT * FROM statistics
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.dwl') 
AND date(created_ts, 'auto') = '2023-07-23'
AND time(created_ts, 'auto') >= '09:00:00'

And corrected this with (BE CAREFULL WITH THE UPDATE STATEMENT)

update statistics set sum = sum - 740470
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.dwl') 
AND date(created_ts, 'auto') = '2023-07-23'
AND time(created_ts, 'auto') >= '09:00:00'

However it appeared that after this correction I was pushing my error forward. So after two pushing forwards I dived a little bit deeper and found the Short Term statistics:

SELECT * FROM statistics_short_term
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.dwl') 
AND date(start_ts, 'auto') >= '2023-07-25'

And the correction (again be careful):

update statistics_short_term set sum = sum - 740470
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.dwl') 
AND date(start_ts, 'auto') >= '2023-07-25'

Think there is only a need to changes the last couple of hours in the Short Tem Statistics!
And off course do not forget to update also the statistics again.

CONCLUSION
So if you need to correct your statistics you might need to correct your short term statistics also to avoid that your error is reappearing.

Hope this is helpful for someone. Please so not use my examples by the letter, do not screw up your database, make frequent back-ups, work initial from a copy; I do this all the time. :smiley: