Insert historical values to statistics table?

Configuration
1

I would like to insert historical sensor values directly to statistics data table
(SQL Inserts)

Can you please advise if:

  • Would be that safe for HA ?
  • Does HA needs correct ID sequences or (I hope) on created / start fields ?

** EDIT **

For example - my sensor (energy return) has id=46
As you can see below DB has historical entries started from day Ive created that sensor 17.III.2022

Question is if I can safely insert historical records (prior 17th of Match), but obviously with higher IDs

obraz
obraz1250Ɨ433 12.5 KB

Im mostly interested to add historical energy production / consumption data

1 Like
3

Guys - any thoughts on this ?

4

Iā€™m currently attempting to do the same.

Did you make any progress on this?

5

Have you ever found a solution?

6

I have seen that frenckā€™s spook integration has a service for that - However, no guarantee this works.
frenck has a lot of disclaimers for that integration.

I will be testing this in the next week as I think this will be the best way back from postgresql to sqlite.

7

Did you have any success with the Recorder service from spook integration? Iā€™m currently trying to ā€œimplementā€ process for manually entering values from my energy/water meters and I usually only do that when I feel like. (But itā€™s usually at least once a month.)
Problem is that when I enter the meter value, the increase of the value shows in the Energy dashboard as a consumption for the time of the entering of the value.
What Iā€™d like to do to split the current value into multiple data points in history between the previous and current readouts.
For example:
I did a readout on 1st: 1000 then next on 5th I enter value of 1400 and Iā€™d like to automatically fill the history with values for 2nd = 1100, 3rd = 1200, 4th = 1300. Whit my goal to have continuous graph for monthly consumption in the energy dashboard in case I for example miss a month.

8

To be honest: I am heavily procrastinating on the migration from PostgreSQL back to sqlite which I wanted to solve with this service.

Regarding your problem: I am doing something similar but on a weekly basis. With this resolution, I am happy with the monthly values

9

Can someone please help me understand how the tables in the database refer to the statistics?

Discussion continued in the Energy category

10

I have a weather station for more than a year that is connected to wundergroud and HA, but because ā€˜ā€˜reasonsā€™ā€™ I have a pretty new Home Assistant installation, so all the data gathered in HA for temp sensors was lost. Iā€™ve created a script to pull data from WU website and generated the data needed for Spook Recorder Statistics. I leave you a screenshot here. There were around 36k rows in data section. It worked as magic!

Screenshot 2025-02-26 223133
Screenshot 2025-02-26 2231331362Ɨ1055 51.4 KB

11

This one seems to work quite well

Now some caveats :

  1. when I exported, some ā€œunitlessā€ counter came with a blank unit in the tsv, the import failed on that, I guess I can just enter whatever and tell the GUI to assume target unit
  2. It seems to take some time for the data to be uploaded into my external mariadb. But when I go to the ā€œHistoryā€ page I can see the data after a few seconds/minutes. The statistics db takes a bit longer . No idea where the data goes in the meantime.
    Anyway I was happy to find that integration. YMMV, BoL.