Script for building Riemann Sum of Power Sensor history in InfluxDB

[GregorEstebanCardosoSchiller]

Hi,
I wanted to give something back to the community for a while and after the API-History-Data to Home-Assistant Script by loeffelpan (#54) for usage of history Data within HA itself and our issues here with the Senec WEB-API Energy (Sum) sensors, I came up with the following idea:

Since "day one" the power sensors are working flawlessly. So I built my own Riemann Sum sensors to be independent from API and the Senec inverter, but I "just" created them so I have this loose history data within grafana/influxdb (from the senec energy sensors of the inverter, then WEBAPI....). So I was thinking, it would be wonderful if I could simply "cumulate" these power sensor values since day one, but with the specific timestamps to be able to evaluate them properly. And yesterday´s evening it just worked (you simply put a file into the config folder (cd config) via your file editor/visual code/samba and use the terminal addon, enter "docker exec -it homeassistant bash" and enter "python3 yourscriptname.py") :

import influxdb
from datetime import datetime, timedelta
from dateutil import parser

# Set up your InfluxDB connection parameters here
host = 'localhost'  # or the IP address of your InfluxDB host
port = 8086
user = 'YOURDBUSERHERE'
password = 'YOURDBPWHERE+'
dbname = 'YOURDBNAMEHERE'

# Connect to InfluxDB
client = influxdb.InfluxDBClient(host, port, user, password, dbname)

# Define your query here. Adjust the time range as needed
query = """
SELECT mean("value") AS "value" FROM "W"
WHERE "entity_id" = 'senec_speicher_solar_generated_power'
AND time >= '2023-01-01T00:00:00Z' 
AND time <= '2024-01-26T00:00:00Z'
GROUP BY time(1h) 
"""

# Execute the query
result = client.query(query)
# Initialize variables
total_energy_wh = 0
last_time = None
points = []

first_point = True
for point in result.get_points():
    #print(point)  # Debugging output to see the structure of 'point'
    current_time = parser.parse(point['time'])

    if last_time is not None:
        time_diff = (current_time - last_time).total_seconds() / 3600
        
        # Check if 'value' is None
        if point['value'] is None:
            #print(f"Skipping point at {current_time} because value is None")
            continue

        energy_wh = point['value'] * time_diff
        total_energy_wh += energy_wh

        influx_point = {
            "measurement": "Wh",
            "tags": {
                "entity_id": "pv_generated_sum"
            },
            "time": current_time.isoformat(),
            "fields": {
                "value": total_energy_wh
            }
        }
        points.append(influx_point)

    last_time = current_time

# Check if points are being prepared correctly
#print(f"Prepared points for InfluxDB: {points}")

# Write points to InfluxDB
if points:  # Ensure there are points to write
    success = client.write_points(points)
    print(f"Data written to InfluxDB: {success}")
else:
    print("No points to write to InfluxDB.")

What does this script do? It simply sums up the watts to hourly intervals and by this creates the Wh and the referencing timestamp. In this case you can see a new DB entry created by the name "pv_generated_sum" that you can find under "Wh" with its entries. With this entity you can create your grafana evaluations over the specific intervals. The source for this sensor was my power sensor since day one "senec_speicher_solar_generated_power".

Here are some peeks:

The problem/dirty solution for going forward:

So as you can see by the datespan in the code this works flawlessly. Now it gets a little dirty to work with this sensor going forward. I needed to create 2 sensors within home assistant. because the riemann sum always starts at zero. So I had to take the current value of the sum (something over 15 Million) and create a template sensor, which uses the "normal" riemann sum of the PV generated power and adds this value. This sensor then is created (or named) within home assistant so it starts its recording in home assistant with the current "initital" value. Otherwise the value would simply drop in influx db to zero because the recording starts in home assistant with 0.

I tried to build a simple template sensor with the name "pv_generated_sum" gave it the state of 15 million, deleted it and build a riemann sum helper with the same name. It started at 15 Million but dropped immediately.
So the only way I saw was to create the actual riemann as helper and the "final" sensor as helper+15 Million so it continues flawlessly within Influx. Although it is possible to not feed influxdb without the helper sensor (so no data trash is created) it is still just a workaround, which does not satisfy me. So if somebody has an idea to "initialize" a riemann sum sensor with a value X, just tell me.

But this solution works for every power sensor, if you want to create a riemann sum sensor within InfluxDB with history.

[GregorEstebanCardosoSchiller]

And I am currently working on the CSV import of the senec API for the complete history.