Hi @Lasoul ,
first of all, thanks a lot for this work.
I still encounter one problem, but perhaps (hopefully) it is just a misunderstanding from my side.
So here comes the explanation (btw: I’m limiting my explanation to water, but I have the same behaviour for all entities [water, gas, solar, elec_in and elec_out]).
On August, 29th in the middle of the day I installed a water-meter and the values are coming in. For the last ~600 days I have daily usage values of my water consumption, so I wanted to use your fantastic project.
Here are my steps:
- On Sep, 5th at 11:05pm (so the last hourly statistic run was finished) I created a template sensor, which takes as state the sensor-value of my water meter
- I waited 5 minutes so that in statistics_meta the metadata_id was created for that new sensor
- I shut down homeassistant completely and started with HeidiSQL to work on my MariaDB instance
- First I exported with your provided SQL the existing values from the water-meter and saved this to “water_high_resolution.csv”
- From the csv I removed the first 8 entries (the hourly statistics from August, 29th), so that I have hourly data starting from August, 30th
- I looked at the first state-value in the csv and used that to calculate back the sensor-values from my historical data and as timestamp I always used 10pm of each day
- I saved the historical data as “water_low_resolution.csv”
- So in “water_high_resolution.csv” I have the hourly data starting from August, 30th and in “water_low_resolution.csv” I haev the daily data from Nov, 18th 2022 to August, 29th 2024
- I imported the the two csv files as explained
- For your big SQL-script I only used “sensor_id_water”, as “sensor_id” I used the metadat_id of the newly created template-sensor and as correction-value I used “1.0”
- Import worked as expected and after starting HA I was able to see in the energy-dashboard the hourly data-usage back to August, 30th and earlier just one value at 10pm for the daily usage !!! PERFECT !!!
–
But then midnight came and the new hourly statistics were created and I got a value of -216000 L shown in the dashboard
Afterwards the next hourly statistics all look fine, it is just the first hourly statistic after the import and this completely breaks (as you can imagine) the display and calculations like week, month, year.
–
In the dev-tools I see no outlier, which I would be able to correct.
–
Checking the statistics-table I see the following: - In the sum-column of my original water-meter I see the summed up values starting from the installation on August, 29th
- BUT In the sum-column of my template-sensor I see the summed up values from everything I imported with your script (till Sep, 5th 11:00pm) and starting from Sep, 6th 0am it falls back to 293 L which is the value from the sum-column of the template-sensor in the table statistics_short_term
–
Do you have any hints/tips/tricks what I can do, or what I have done wrong?
Thanks a lot in advance
Joerg