Insert Historical Power and Gas Data into database

aadbloem · December 26, 2021, 12:06pm

I managed to put almost three years historical data downloaded from my enelogic.com account into the Home Assistant sqlite database table -statistics-. I calculated the attribute -sum- correctly after import and the graphs are great but as soon as Home Assistant is connected to my energy metering to add current metering the historical data graphs get garbled.
I do not know why and I cannot find a reason.
Anybody ??

aadbloem · December 28, 2021, 9:19pm

I have discovered that Home Asisstant, once running again with 3 years history added, does not pick up the most recent sum (calculated including history) from the statistics table but from somewhere else so the software continues with the wrong sum value for table statistics. Where is this wrong sum value coming from?

ondras12345 · December 29, 2021, 9:44am

From looking at the source code, it seems like there are 5-minute statistics that you’d need to modify as well as long-term (hourly) statistics:

github.com

home-assistant/core/blob/4f25e98999439d46101a436bcecc618274ff0686/homeassistant/components/recorder/statistics.py#L265-L273

    
      
          def compile_hourly_statistics(
              instance: Recorder, session: scoped_session, start: datetime
          ) -> None:
              """Compile hourly statistics.
          
          
    This will summarize 5-minute statistics for one hour:
              - average, min max is computed by a database query
              - sum is taken from the last 5-minute entry during the hour
              """

aadbloem · January 3, 2022, 11:00am

Thanks and I did.
The 5 minutes statistics are in table statistics_short_term and my python program recalculates the sum values in this statistics_short_term table. Thats is the trick, recalculate sum values in table statistics and in table statistics_short_term. I now have a stable history in my Hassio. I have Hassio on Debian Linux and I stop docker during the load and the sum recalculation sum values. Stopping docker prevents inserting 5 minute values. On a not docker implementation one can disconnect the P1 USB cable of Youless etc.