Import old energy readings for use in energy dashboard

Redwid · November 28, 2022, 11:58am

By using cumulative sum looks usge is correct now for me:

Lakini · November 28, 2022, 3:00pm

Which by the way makes me wonder how to import old data into a fresh installation which started at 0 for a power meter entitiy. At least for one hour, where old meets new data, you would have a massive negative bar…

Probably only works when you define a seprate “dummy entitiy” to the old data, dump the old data in, and then have a “switchover day” from which on only the new entities data is added

Redwid · November 28, 2022, 3:47pm

Might be last_reset property supposed to help here?

github.com

home-assistant/core/blob/987cf8b9d0b513d4ef184811aac2c75804140cfb/homeassistant/components/recorder/websocket_api.py#L461

    
      
                      vol.Required("source"): str,
                      vol.Required("statistic_id"): str,
                      vol.Required("unit_of_measurement"): vol.Any(str, None),
                  },
                  vol.Required("stats"): [
                      {
                          vol.Required("start"): cv.datetime,
                          vol.Optional("mean"): vol.Any(float, int),
                          vol.Optional("min"): vol.Any(float, int),
                          vol.Optional("max"): vol.Any(float, int),
                          vol.Optional("last_reset"): vol.Any(cv.datetime, None),
                          vol.Optional("state"): vol.Any(float, int),
                          vol.Optional("sum"): vol.Any(float, int),
                      }
                  ],
              }
          )
          @callback
          def ws_import_statistics(
              hass: HomeAssistant, connection: websocket_api.ActiveConnection, msg: dict[str, Any]
          ) -> None:

Lakini · November 28, 2022, 4:56pm

Would make sense with what is documented in Sensor Entity | Home Assistant Developer Docs

But the logic there would have to be an interesting one. Let’s say you setup a new system on a Monday, and your (new) energy meter entity starts it’s thing, and the statistics get recorded. When you now on the tuesday re-import old energy readings from the time before into the same new entity, the import job would not only have to get the right old data in, but would also have to adjust all the sum values for the new entity.

reox · November 29, 2022, 10:42am

I think the issue is that you cannot really mix statistics import with sensor states. I tried to import statistics but also set the sensor value, which messed up the statistics. Now, I never touch the sensor and only import statistics, which works great so far.
I would say there is probably a way to do that, but you would need to somehow forbid the statistics module to load new sensor data.

I guess a much cleaner way would be to import historical data into the sensor and let the statistics module do its thing. That would however mean, you might need to re-do the whole statistics, for example if current values already exist and you want to patch in missing values.

Redwid · November 29, 2022, 11:15am

@reox have you create any ghost/fake sensor for imported staistics? I see that home-assistent is throwing a warning if I’ll have no sensor but only stats.

reox · November 29, 2022, 11:16am

I have a sensor, but I never set a state there, thus it always shows “unknown”. That works fine for me - with the disadvantage, that you do not see the current total value.

VaReTaS · December 1, 2022, 9:10am

Hi,

I was following up on this, but I am not sure how to manage the code to match my Excel file.

Anyone could provide some hints on how to address this file to get it imported into HA?

I have around 42700+ lines,with hourly records from 01/01/2020 to 15/11/2022

le_top · December 1, 2022, 10:44am

We just turned december and my last imported gaz consumption was on November 24th, so I have 6 days that are going to count towards consumption in december, which is very annoying as it will make comparing november and december consumption with the previous year invalid.

My gaz distributor did a maintenance/upgrade of his website and while the web grabbing method still worked, the data on the site was not updated over the period - it was just updated today, december 1st.

I noticed that when I added my water meter to the energy dashboard - a function that was recently added - all historical data is available in the energy dashboard - it goes back to june:

So importing historical data was possible for add a water meter to the dashboard.

It should be possible to add meter values to the database at a given date, not just for “now”. That would allow importing long term historical data, or cope with the unavailability of data from the energy provider for whatever reason.

nochanceofmeatballs · December 13, 2022, 9:15pm

I just wanted to add my wish for making import and export CSV specifically for energy history, available on the GUI. There’s quite a few edge cases where someone may want to either start from scratch but simply retain energy history, or move energy history around different installs easily.

I know it’s do-able via APIs, SQL, whatever, but adding CSV import and export via gui would be real nice. I’ve already had three experiences in 18 months of losing history and having to recover, so for those less able it would also be quite reassuring to have the data tangibly accessible and recoverable.

On the import it could possibly have import (sum) and import (replace) for example. Or mapping fields to existing or new datasets. It could get complex!

le_top · December 18, 2022, 4:14pm

I am interested in learning about the API that allows adding “history” at a given time in the past.

nochanceofmeatballs · December 19, 2022, 11:19am

I should say 'part-do-able" via API. I’m not aware of any way to import history this way, but was thinking of “export” of data this way.

Key point was via the UI.

ldotlopez · January 23, 2023, 9:13am

Hi, everyone.

I’m interested in this topic too and I have some code that you may find useful.

“Historical Sensors” it’s a helper module to build sensors with historical data. It has the necessary code to import data but the UI for uploading and importing CSV files is still missing. Once it’s figured out how to present the UI it should be trivial.

bertoxxulous · February 5, 2023, 4:25pm

I’m unfortunately getting an error when loading this integration. A shame, since it really sounds like just what I need.

homeassistant.requirements.RequirementsNotFound: Requirements for delorian not found: ['homeassistant-historical-sensor==0.0.2.dev1']

ldotlopez · February 6, 2023, 4:48pm

Hi @bertoxxulous

The ‘delorian’ integration it’s just a test demo for the module, there is no integration ‘per-se’ in my project

The delorian integration depends on homeassistant-historical-sensor module so you have to install the module in the venv of homeassistant running pipenv install -e . or whatever suits you.

Anyway, I have made some changes to make delorian pull homeassistant-historical-sensor from github.

I hope it works for you.

WallyR · February 26, 2023, 2:44am

I just stumbled on Spook by Frenck.
The description of service calls look quite interesting.
I have not yet tried it though.

johnavich · March 17, 2023, 3:01am

As interesting as this is, i’d LOVE to get a detailed writeup on how to use this websocket API call, so i can write a method to interact with it. I don’t see any documentation on what it expects, examples of usage, anything.

WallyR · March 17, 2023, 7:33am

The developer tools shows this for a service call.

VaReTaS · March 17, 2023, 9:48am

For that to appear, as you said, you need to install the Spook integration (GitHub - frenck/spook: Spook 👻 Not your homie).

Anyhow, I am not sure how that may work.

WallyR · March 17, 2023, 10:43am

I think Spook is the only way to do this.
It has been a long standing decission by developers that import of historic data was not something they wanted to go into.
Frenck has therefore added it to the Spook integration instead, but I guess this is done by a direct SQL call and not through some API that will be available elsewhere.