Add or show some more data in the energy dashboard by manipulating HA's SQLite-DB

[loeffelpan]

Thanks for your improvements to this integration.
I'm using it in favor of https://github.com/mchwalisz/home-assistant-senec since the software upgrade to v825. Also the API-sensors to replace the missing statistics data are great.

As I started with HA couple of month later than my PV was installed there are some data missing in the energy dashboard.

I took a look on your API implementation and I'm wondering if there is any way to get historical statistics from that API. Do you have any documentation of the API? In which cases do I have to use the "old" endpoints?

Would be glad if you can give me some of your knowledge to maybe get these historical statistics.

UPDATE:
In this discussion we documented two suggestions to add or show some more date in the energy dashboard.
First is to get data from old sensors (before v825). Please read here.
Second is a powershell script to create sql statements with api data from senec for adding that data to your energy dashboard.
You can get the script here.

[marq24]

Sorry for the late reply... Actually the source of my "knowledge" is first of all the work from @mstuettgen (https://github.com/mstuettgen/homeassistant-addons/tree/main/senecweb2mqtt) - and then based on that I am using the "DevTool" build into the Webbrowers in order to analyse request/response (in the network-tab) while I use mein-senec.de web portal...

With the network tab you should see the request/URL's when accessing historic data - this can be used then also in the pysenec_ha ...

But I am not aware that there is a documented way, how to get data into the EnergyDashboad of HA... but if you succeed then I would be also interested in your approach... For now I still use a Excel to get the final "valid" data over time (for my personal "I did the right thing" feeling... And when I would finally try to merge the data, then I probably will do that via the additional InfluxDB...

[loeffelpan]

If you have you whole data in Excel/CSV it's possible to add that to the energy dashboard with some DB inserts to sqlite-db of Homeassistant. I will show you.

How do you get all your (valid) data in Excel?
I tried to copy values (manually Copy&Paste) from mein-senec.de but the addition of day values doesn't match annual values. And CSV files from mein-senec.de do not contain energy but power with different time amounts (+/- 5min). Hard to calculate energy values from that data.

Hopefully the API gives valid data (matching those in the senec App).
But by know I'm failing with 403 Forbidden using classic login (app-gateway). And mein-senec-url seems not to be an API (returns a HTML page saying no login possible without JavaScript.
Maybe @mstuettgen can join the discussion?

[marq24]

I got my "valid" Data from here - https://mein-senec.de/endkunde/api/statistischeDaten/download?anlageNummer=0&woche=37&jahr=2023 - and of course altered the URL to get all the... all by hand - not by code - then renamed toe csv's to txt (since this will auto-trigger the excel csv2ecxel function (instead of going the Text2Table function)...

But this of course just provide you with daily values...

When you use python code - make sue that you use the request lib in order to login to mein-senec.de - aiohttp have currently a bug (that will be fixed in 3.9 - since I provided a PR for that)...

[loeffelpan]

That's exactly what I mean with:

And CSV files from mein-senec.de do not contain energy but power with different time amounts (+/- 5min). Hard to calculate energy values from that data.

That files contain just power data in around 5 min intervals. Totally useless for HAs energy dashboard.
I tried to calculate energy values from that data with powershell. But that doesn't match the values from Senec either.

I would like to achieve scripted access to energy data from senec to fill in the energy dashboard of homeassistant.
Daily values would fit my needs. I never need to look at hours in the past. Usually days, weeks, month and years would be enough. So I aggregate data from one day at the first hour. This way the days, weeks, month and will be displayed correctly.

[loeffelpan]

There are some opportunities manipulating HA's SQLite-DB to add or show some more data in the energy dashboard.
I strongly recommend to make a copy of the DB first, try those things with e. g. SQLiteBrowser64 and review the manipulated values by hand.

First step get some historic data in HA's energy dashboard is to change metadata_ids from old "total"-sensors to new ones.
This is about to see data HA recorded with old sensors in your energy dashboard.

In my case I had to start bash inside my homeassistant docker container and browse into the database with some sqlite3 commands.
In the official docker image there's no sqlite-package installed. You can add that with apk add sqlite.
Depending on the way you're using/installing homeassistant the way to access the DB may differ.

nightfury@ToothLess:~$ sudo docker exec -it homeassistant bash

ToothLess:/config# sqlite3 home-assistant_v2.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> SELECT * FROM statistics_meta;
...
5|sensor.senec_house_power_total|recorder|kWh|0|1|
6|sensor.senec_solar_generated_total|recorder|kWh|0|1|
7|sensor.senec_battery_charged_total|recorder|kWh|0|1|
8|sensor.senec_battery_discharged_total|recorder|kWh|0|1|
9|sensor.senec_grid_imported_total|recorder|kWh|0|1|
10|sensor.senec_grid_exported_total|recorder|kWh|0|1|
...
53|sensor.senec_webapi_consumption_total|recorder|kWh|0|1|
54|sensor.senec_webapi_powergenerated_total|recorder|kWh|0|1|
55|sensor.senec_webapi_accuimport_total|recorder|kWh|0|1|
56|sensor.senec_webapi_accuexport_total|recorder|kWh|0|1|
57|sensor.senec_webapi_gridimport_total|recorder|kWh|0|1|
58|sensor.senec_webapi_gridexport_total|recorder|kWh|0|1|
...

Should show something similar.
Be aware to change the IDs correctly. The ordering and numbering of the entitys is different in any HA setup.
To change ids you can use statements like this.

UPDATE statistics SET metadata_id = 53 WHERE metadata_id = 5;
UPDATE statistics SET metadata_id = 54 WHERE metadata_id = 6;
UPDATE statistics SET metadata_id = 55 WHERE metadata_id = 7;
UPDATE statistics SET metadata_id = 56 WHERE metadata_id = 8;
UPDATE statistics SET metadata_id = 57 WHERE metadata_id = 9;
UPDATE statistics SET metadata_id = 58 WHERE metadata_id = 10;

[loeffelpan]

Feel free to link or copy this, if it might be interesting for others who had to change to WEBAPI-sensors.
Is it correct that old recorder-entitys are completely removed from your integration until the firmware are not providing those data?

[marq24]

just as information to all others - these metadata_ids might be completely different ones in your HA installation - so please be careful when copy and paste the UPDATE statements - you will have extremely likely different id's

[loeffelpan]

Absolutely correct.
This is for advanced users. But if someone fails and crashes the DB the most bad case is to loose history of all entities. It will not break the setup and no settings, integrations and dashboards will be affected.

I edited my post to be more clear.
Maybe you can link or re-post this for other users as noone would likely find this with our conversation title.

[marq24]

I just altered the Title and will link this to the EnergyDashboard Issue...

[marq24]

Is it correct that old recorder-entitys are completely removed from your integration until the firmware are not providing those data?

no & yes - the old entities are still in the code - as long as the SENEC will not provide any values in the STATISTICS the integration will not add these "old" sensors... But as soon as the SENEC will provide again STAT-data the Entities will reappear after a HA-restart - so you as user should not have to do anything to get them back - (already implemented)

[GregorEstebanCardosoSchiller]

Thanks for your improvements to this integration. I'm using it in favor of https://github.com/mchwalisz/home-assistant-senec since the software upgrade to v825. Also the API-sensors to replace the missing statistics data are great.

As I started with HA couple of month later than my PV was installed there are some data missing in the energy dashboard.

I took a look on your API implementation and I'm wondering if there is any way to get historical statistics from that API. Do you have any documentation of the API? In which cases do I have to use the "old" endpoints?

Would be glad if you can give me some of your knowledge to maybe get these historical statistics.

UPDATE: In this discussion we documented two suggestions to add or show some more date in the energy dashboard. First is to get data from old sensors (before v825). Please read here. Second is a powershell script to create sql statements with api data from senec for adding that data to your energy dashboard. You can get the script here.

Hi, so I tried your script with 2021-08-01 to 2023-11-27

It always "broke" because at:

if ($state_isNull -or $sum_isNull) {
Write-Host ('At least one state or sum is missing at {0} in db copy.' -f $latest) -BackgroundColor Red
break
}

First I think there should be written "$ts_latest" - $latest is not referenced
Second: The Unix Timestamp generated is different to the actual timestamp. I tried it locally under windows. Why is that? #
Third: Am I correct that the outcome should be updated webapi sensors? Or only statistics in energy dashboard?

[fugazzy]

Hello,
thank you for this guide to get the older data into the energy dashboard.

Last september (I think) I switched to the local LAN version of this integration, using template sensors to get the total values for solar, house, grid and battery.
Yesterday added the WebAPI version and switched the energy dashbord to the new sensors.
With this guide I managed to get the old data (before v825, last september) into my enery dashboard. Very nice, thank you!

What I am now missing is the data of the period in between using the template sensor (riemann sum) to integrate these into the dashboard.
`sensor:

Verbrauch

• platform: integration
  source: sensor.senec_house_power
  name: senec_house_total_consumption_fb
  unique_id: senec_house_total_consumption_fb
  #icon: mdi:home-import-outline
  unit_prefix: k
  round: 2

SOLAR

• platform: integration
  source: sensor.senec_solar_generated_power
  name: senec_solar_total_generated_fb
  unique_id: senec_solar_total_generated_fb
  #icon: mdi:solar-power
  unit_prefix: k
  round: 2

Battery

• platform: integration
  source: sensor.senec_battery_charge_power
  name: senec_battery_total_charged_fb
  unique_id: senec_battery_total_charged_fb
  #icon: mdi:home-battery
  unit_prefix: k
  round: 2

• platform: integration
  source: sensor.senec_battery_discharge_power
  name: senec_battery_total_discharged_fb
  unique_id: senec_battery_total_discharged_fb
  #icon: mdi:home-battery-outline
  unit_prefix: k
  round: 2

GRID

• platform: integration
  source: sensor.senec_grid_imported_power
  name: senec_grid_total_import_fb
  unique_id: senec_grid_total_import_fb
  #icon: mdi:transmission-tower-import
  unit_prefix: k
  round: 2

• platform: integration
  source: sensor.senec_grid_exported_power
  name: senec_grid_total_export_fb
  unique_id: senec_grid_total_export_fb
  #icon: mdi:transmission-tower-export
  unit_prefix: k
  round: 2
  `
  With these sensors I also tried to switch the metadata_id, but this leads to an error: (see attachment)
  "... Runtime error: UNIQUE constraint failed ..."
  

Is there a way to also include this data into the dashboard?

[fugazzy]

OK, I hope to find a way for that. I had not deactivated the older sensors when I switched to WebApi so they run in parallel since yesterday. That seems to be the reason. Thank you!

[fugazzy]

I now have SQLite Web and see the database and the statistic. Could you help me with the query to delete all data since yesterday about 7:15 pm till today ?
For: sensor.senec_solar_total_generated_fb, ID 888

[fugazzy]

something like this?
DELETE FROM "statistics" WHERE metadata_id = 888 AND timestamp >= '2024-30-01 23:00:00' AND timestamp < '2024-31-01 10:00:00'

[loeffelpan]

You're on the right track.
Timestamps have to be unix timestamps in UTC. And you have to name the column for the where clause.
I would use start_ts.

[fugazzy]

Thank you, that worked:
DELETE FROM "statistics" WHERE metadata_id = 887 AND start_ts >= '1706565600' AND start_ts < '17066088000'