Integrate with Home assistant

[toggm]

Integrate with Home Assistant

With the following steps you can push the pv production data from your home assistant instance to a sun-set-analytics instance.

1. Install and start sun-set-analytics docker image

Install sun-set-analytics based on the description in the Readme

2. Provide a shell command to extract statistics data

The following shell script exports the pv production statistics data from the home assistant database. Place it somewhere in the home assistants configuration directory:

#!/bin/bash

SQLITE3='/usr/bin/sqlite3'
EXIT_CODE=0
RESPONSE=$(stat $SQLITE3) || EXIT_CODE=$?
if [ $EXIT_CODE -ne 0 ]; then     
  apk add sqlite 
fi

JQ='/usr/bin/jq'
EXIT_CODE=0
RESPONSE=$(stat $JQ) || EXIT_CODE=$?
if [ $EXIT_CODE -ne 0 ]; then     
  apk add jq 
fi


FROM=$1
if [ -z "$FROM" ]; then
  FROM="1 day"
fi

$SQLITE3 --json /config/home-assistant_v2.db "SELECT strftime('%Y-%m-%dT%H:%M:%SZ', statistics.start_ts, 'unixepoc
h') as 'from', strftime('%Y-%m-%dT%H:%M:%SZ', statistics.start_ts + 3599, 'unixepoch') as 'to', (statistics.state 
- LAG(statistics.state, 1) OVER (ORDER BY statistics.id)) as production  from statistics inner join statistics_met
a on statistics.metadata_id = statistics_meta.id where statistics_meta.statistic_id =  'sensor.energy_produced' an
d statistics.start_ts > strftime('%s', 'now', '-$FROM') ORDER BY statistics.start_ts desc;" | $JQ -c '{"from": .[-
1].from, "to": .[0].to, "measured_production": . | map(select(.production != null))}'

Be aware that:

1. This script only works if home assistant runs with an sqlite database (default)
2. The script installs the sqlite and jq tools through the apk package manage, therefore expects a debian based OS. The installation is executed within the shell script to ensure the tools exist. If you're running the script within a docker image, the installation is need every time after restarting the docker image.
3. replace sensor.energy_produced with the entity_id of the entity measuring the pv production, if not the same
4. Don't put those commands within the shell_command as shell_commands only support a limited set of commands (i.e. no apk, no pipe)

The reference the script from within a shell_command configuration:

- shell_command:
    export_energy_produced: ./configurations/shell_commands/export_energy_produced.sh "{{ energy_produced_date_range }
}"

Adjust the path to the script accordingly.

3. Define REST commands

Define the following REST commands to be able to communicate with the sun-set-analytics instance:

- rest:
    delete_production_data:
      url: $SUN_SET_ANALYTICS_HOST/api/installations/$INSTALLATION_ID/delete-production-data?from={{from}}&to={{to}}
      method: POST
      content_type: "application/json"
      headers:
        Authorization: !secret sun_set_analytics_api_token 
    import_production_data:
      url: $SUN_SET_ANALYTICS_HOST/api/installations/$INSTALLATION_ID/import-production-data
      method: POST
      content_type: "application/json"
      headers:
        Authorization: !secret sun_set_analytics_api_token 
      payload: >- 
        {{ data }}

Replace $SUN_SET_ANALYTICS_HOST with the url through which the application is reachable and $INSTALLATION_ID with the id of the pv installation (defaults to 1).
Create an API-Key and add the key with the sun_set_analytics_api_token name to the home assistants secrets.yaml file.

4. Create a script

Create a script to combine all steps:

sequence:
  - action: shell_command.export_energy_produced
    metadata: {}
    data:
      energy_produced_date_range: 1 day
    response_variable: energy_produced
  - action: rest_command.delete_production_data
    metadata: {}
    data:
      from: "{{ (energy_produced['stdout'] | from_json)['from'] }}"
      to: "{{ (energy_produced['stdout'] | from_json)['to'] }}"
  - action: rest_command.import_production_data
    metadata: {}
    data:
      data: "{{ (energy_produced['stdout']) }}"
alias: sun_set_analytics_export_energy_produced
description: ""

The script calls the export shell_command, deletes all pv production data for the exported range and re-imports the data.

5. Schedule export job

In the end, setup a scheduled job (i.e. with an automation) to regularly (daily) run the previously defined script.

Manually import long term data

Home assistant doesn't allow to import long term data this way as a script can only receive a very limited number of bytes as data. If you want to import a longer period of data, you'll have to trigger the import manually by:

1. Run the shell script manually, providing a longer range of data to export and write it to a file, i.e. /config/configurations/shell_commands/export_energy_produced.sh "6 years" > /tmp/export.json (Execute the script within the docker image or adjust the path to the homeassistant_v2.db database
2. Export the from and to range from the exported json (or define it manually): ha-pi:/config# cat /tmp/export.json | jq '{from: .from, to: .to}' { "from": "2022-02-11T20:00:00Z", "to": "2025-04-16T17:59:59Z" }
3. Delete existing pv production data for this period manually curl -v -H 'Authorization: users API-Key $API_KEY' -X POST "$SUN_SET_ANALYTICS_HOST/api/installations/$INSTALLATION_ID/delete-production-data?from=$FROM_DATE&to=$TO_DATE", replace $SUN_SET_ANALYTICS_HOST, $API_KEY, $INSTALLATION_ID, FROM_DATE and $TO_DATE
4. Import the exported production data curl -v -H 'Authorization: users API-Key $API_KEY' -X POST "$SUN_SET_ANALYTICS_HOST/api/installations/$INSTALLATION_ID/import-production-data" --data @/tmp/export.json, replace $SUN_SET_ANALYTICS_HOST, $API_KEY and $INSTALLATION_ID