The database contains invalid values. How can I prevent/correct this? (SolarmanPV)

Configuration
1

Hi,

I have connected a REST API and read values from my PV system there. The value is the total amount of produced electricity (meter reading).

Unfortunately, the API is sometimes not correctly accessible and unfortunately 0 values are entered into the database. Thus, in the statistics then as soon as a correct value comes again the sum is much too high. It then calculates from 0 to the current value.

I have already created a few SQL statements that correct this but this can not be the permanent solution.

Rest API Call:

  - platform: rest
    resource: https://api.solarmanpv.com/device/v1.0/currentData?appId=1234567890123456&language=en&=
    method: POST
    name: "Cumulative Production kWh"
    scan_interval: 120
    headers:
      Authorization: bearer xyz
      Content-Type: application/json
    payload: '{  "deviceSn": "12345678"}'
    value_template: "{{ value_json.dataList[29].value if value_json.dataList[29].value | float > 0 }}"

SQL to fix wrong values:

UPDATE "statistics" 
set sum = state
where
or metadata_id = 36
or metadata_id = 72


delete
FROM "states"
where entity_id = "sensor.cumulative_production_kwh"
and state = "0.00"

delete from statistics where metadata_id = 72 and state = 0

How do I prevent the 0 values from being entered into the database or how can I have the database correct it automatically.

2

Assuming you mean dataList[29] is not always in the payload, to keep the previous value:

value_template: "{{ value_json.dataList[29].value if value_json.dataList[29].value | float(0) > 0  else states('sensor.cumulative_production_kwh') }}"