How to import historical energy data

Share your Projects!
, ,
41

Top werk! Will try it asap and report back.

42

Great, will check it out and let you know my experiences.

43

MariaDB import works perfectly now. 7 years of energy and solar history imported. No idea what I will do with it now though, besides looking at it :wink:

Thanks for your efforts!

1 Like
44

Hi,

I have a problem in the Energy Dashboard because Homewizard changed some sensors and now I have the solar energy double from february 2023 till december 2023.

Snag_3332d81c
Snag_3332d81c1808×665 26.6 KB

When I delete the old sensor I miss all the history till october 2021.

Snag_333cd79f
Snag_333cd79f556×629 29.9 KB

So that’s why I tried this import option but it didn’t go well and I lost all the data from the kWh meter.

What I did was the following, I created a csv file with the data from October 2021 till 1 feb 2023 coming from homewizard.

This a part of the csv file i created called elec_solar_high_resolution:

1631296800;0.002
1631297700;0.002
1631298600;0.002
1631299500;0.002
1631300400;0.002
1631301300;0.002
1631302200;0.002
1631303100;0.002
1631304000;0.003
1631304900;0.005
1631305800;0.006
1631306700;0.007
1631307600;0.008
1631308500;0.009
1631309400;0.010
1631310300;0.012
1631311200;0.013
1631312100;0.014
1631313000;0.015
1631313900;0.016
1631314800;0.017
1631315700;0.018
1631316600;0.019
1631317500;0.020
1631318400;0.021
1631319300;0.022
1631320200;0.023
1631321100;0.024
1631322000;0.025
1631322900;0.026
1631323800;0.027
1631324700;0.028
1631325600;0.029
1631326500;0.030
1631327400;0.031
1631328300;0.032
1631329200;0.033
1631330100;0.034
1631331000;0.036
1631331900;0.037
1631332800;0.038
1631333700;0.039
1631334600;0.040
1631335500;0.041
1631336400;0.042
1631337300;0.043
1631338200;0.044
1631339100;0.045
1631340000;0.046
1631340900;0.047
1631341800;0.048
1631342700;0.049
1631343600;0.050
1631344500;0.051
1631345400;0.053
1631346300;0.055
1631347200;0.056
1631348100;0.056
1631349000;0.056
1631349900;0.056
1631350800;0.056
1631351700;0.056
1631352600;0.056
1631353500;0.056
1631354400;0.056
1631355300;0.056
1631356200;0.056
1631357100;0.056
1631358000;0.056
1631358900;0.056
1631359800;0.056
1631360700;0.056
1631361600;0.056
1631362500;0.056
1631363400;0.056
1631364300;0.056
1631365200;0.056
1631366100;0.056
1631367000;0.056
1631367900;0.056
1631368800;0.056
1631369700;0.056
1631370600;0.056
1631371500;0.056
1631372400;0.056
1631373300;0.056
1631374200;0.056
1631375100;0.056
1631376000;0.056
1631376900;0.056
1631377800;0.056
1631378700;0.056
1631379600;0.056
1631380500;0.056
1631381400;0.056
1631382300;0.056
1631383200;0.139
1631384100;0.255
1631385000;0.333
1631385900;0.393
1631386800;0.433
1631387700;0.463
1631388600;0.486
1631389500;0.489
1631390400;0.489
1631391300;0.489
1631392200;0.489
1631393100;0.489
1631394000;0.489
1631394900;0.489
1631395800;0.489
1631396700;0.489
1631397600;0.489
1631398500;0.489
1631399400;0.489
1631400300;0.489
1631401200;0.489
1631402100;0.489
1631403000;0.489
1631403900;0.489
1631404800;0.489
1631405700;0.489
1631406600;0.489
1631407500;0.489
1631408400;0.489
1631409300;0.489
1631410200;0.489
1631411100;0.489
1631412000;0.489
1631412900;0.489
1631413800;0.489
1631414700;0.489
1631415600;0.489
1631416500;0.489
1631417400;0.489
1631418300;0.489
1631419200;0.489
1631420100;0.489
1631421000;0.489
1631421900;0.489
1631422800;0.489
1631423700;0.489
1631424600;0.489
1631425500;0.489
1631426400;0.489
1631427300;0.489
1631428200;0.489
1631429100;0.489
1631430000;0.489
1631430900;0.489
1631431800;0.489
1631432700;0.498
1631433600;0.538
1631434500;0.607
1631435400;0.670
1631436300;0.768
1631437200;0.890
1631438100;1069
1631439000;1297
1631439900;1468
1631440800;1743
1631441700;2115
1631442600;2658
1631443500;3251
1631444400;3662
1631445300;4081
1631446200;4665
1631447100;4961
1631448000;5401
1631448900;5970
1631449800;6937
1631450700;7701
1631451600;8458
1631452500;9360
1631453400;10160
1631454300;10956
1631455200;11716
1631456100;12482
1631457000;13184
1631457900;13998
1631458800;14814
1631459700;15475
1631460600;16217
1631461500;16819
1631462400;17382
1631463300;17893
1631464200;18364
1631465100;18695
1631466000;19113
1631466900;19497
1631467800;19896
1631468700;20258
1631469600;20597
1631470500;20896
1631471400;21148
1631472300;21289
1631473200;21448
1631474100;21638
1631475000;21720
1631475900;21753
1631476800;21755
1631477700;21755
1631478600;21755
1631479500;21755
1631480400;21755
1631481300;21755
1631482200;21755
1631483100;21755
1631484000;21755
1631484900;21755
1631485800;21755
1631486700;21755
1631487600;21755
1631488500;21755
1631489400;21755
1631490300;21755
1631491200;21755
1631492100;21755
1631493000;21755
1631493900;21755
1631494800;21755
1631495700;21755
1631496600;21755
1631497500;21755
1631498400;21755
1631499300;21755
1631500200;21755
1631501100;21755
1631502000;21755
1631502900;21755
1631503800;21755
1631504700;21755
1631505600;21755
1631506500;21755
1631507400;21755
1631508300;21755
1631509200;21755
1631510100;21755
1631511000;21755
1631511900;21755
1631512800;21755
1631513700;21755
1631514600;21755
1631515500;21755
1631516400;21755
1631517300;21755
1631518200;21755
1631519100;21761
1631520000;21796
1631520900;21878
1631521800;22002
1631522700;22153
1631523600;22312
1631524500;22503
1631525400;22744
1631526300;22980
1631527200;23223
1631528100;23528
1631529000;23875
1631529900;24330
1631530800;24735
1631531700;25135
1631532600;25835
1631533500;26499
1631534400;26990
1631535300;27605
1631536200;28216
1631537100;29025
1631538000;29788
1631538900;30512
1631539800;30912
1631540700;31265
1631541600;31625
1631542500;32091
1631543400;32558
1631544300;32950
1631545200;33322
1631546100;33675
1631547000;34108
1631547900;34494
1631548800;34897
1631549700;35229
1631550600;35561
1631551500;35888
1631552400;36213
1631553300;36611
1631554200;36945
1631555100;37251
1631556000;37555
1631556900;37819
1631557800;38012
1631558700;38133
1631559600;38213
1631560500;38274
1631561400;38311
1631562300;38324
1631563200;38324
1631564100;38324
1631565000;38324
1631565900;38324
1631566800;38324

Then I followed the steps and find the sensor ID for the kWh meter I want to update with the historical data. Changed ID nr in the script for the correct one in my database for the kWh meter. I changed the correction to 1.0 because both measurements are in kWh. I didn’t know what to do with the cut of new meter and cut of invalid values and I changed both to 1.0.

Then I Imported the csv and processed the script. When I restored the database and restarted Home Assistant I lost all the data for this sensor.

What did I do wrong?
I hope someone can help me with this so I can correct the data.

Thanks!

45

You should have kept those values at 25 and 1000.
Now everything above 1kWh (per hour) is considered as an invalid value and deleted.

So for cutoff new meter: 25 means, when the sensor is in kWh that everything between 0 and 25 is still considered a new meter when the new meter case has been detected.

Invalid value: 1000 means, when the sensor is in kWh that every value above 1000 kWh (within an interval) is considered an invalid value and will be deleted.

46

Thanks for your quick response!

With the correct cutoff and invalid value, the current data AND all the history data are now in the Energy Dashboard!!!

Thanks for this great tool and perfect action plan.

1 Like
47

Would this also work for importing historical energy costs? Worked perfect for my total energy usage but the energy cost history doesn’t populate on its own.

48

This is currently not supported.
How would you envision this to work?
For instance: specifying periods with a fixed price for an entity?

49

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

50

I believe the issue may be due to the lack of history for the template sensor.

Home Assistant uses two tables: statistics and statistics_short_term. The statistics table stores hourly interval statistics, while the statistics_short_term table stores 5-minute interval statistics. After a certain period (about a week or so), data from statistics_short_term is rolled over into the statistics table.

The import script assumes that both tables contain complete data, with statistics_short_term fully populated. I’m not sure how the script behaves when these tables are empty or nearly empty.

A quick fix would be to accumulate 7 days of short-term statistics for the template sensor before importing the data. This would ensure a correct rollover from short-term to long-term statistics.

I need to check if the script can detect and correct this issue. The challenge would be generating short-term statistics rather than merely correcting them, as the script currently does.

51

Thanks a lot for your help on that

52

Has anyone had success automating this script? Seems like it involves stopping home assistant and manually importing the data. Was looking at ways to try and get my electric company’s data into HA, which is always a day behind and published as excel, but most of the automations I’d like to do would be rendered moot if I have to manually add the data.

53

It would be very nice to have an import option in the UI. These conversion scripts are a great starting point.

I have some events which mess up the monthly statistics. By either reimporting accurate data fromy energy provider or manually fixing the values in excel would be a nice feature for many users.

1 Like