Helfer-Entitäten direkt in SQL Abfragen verwenden

Allgemein
, ,
1

Liebe Community,

hat schon jemand z.B. mit dem HACS Repository “ha-sql_json” eine SQL Abfrage mit yaml-Verweis auf eine Helfer Entität erstellt?

Ich möchte gerne folgende Abfrage realisieren. Habe bisher aber mit meinen Versuchen bisher keinen Erfolg gehabt:

query: >
   SELECT
     ROUND(
       (SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.gasverbrauch_kwh' AND created_ts < {{ as_datetime(states('input_select.datum_jahresabrechnung_gas')).replace(year=as_datetime(states('input_select.datum_jahresabrechnung_gas')).year + 1) }} ORDER BY created_ts DESC LIMIT 0,1) -
       (SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.gasverbrauch_kwh' AND created_ts > {{ as_datetime(states('input_select.datum_jahresabrechnung_gas')) }} ORDER BY created_ts ASC LIMIT 0,1)
     ,2) AS wert
   column: 'wert'

Die Abfrage liefert unter Verwendung des Startdatums in der Form: 2025-04-19 00:00:00 und des Enddatums ein Jahr in der Zukunft: 2026-04-19 00:00:00 mit der SQL Integration das richtige Ergebnis.

Die Ausdrücke für das Enddatum:

{{ as_datetime(states('input_select.datum_jahresabrechnung_gas')).replace(year=as_datetime(states('input_select.datum_jahresabrechnung_gas')).year + 1) }}

und das Startdatum:

{{ as_datetime(states('input_select.datum_jahresabrechnung_gas')) }}

sind in den Entwicklertools positiv getestet und liefern die richtigen Ausdrücke.

2

Müssen bei dem Repository die SQL-Statements nicht mit Semikolon ( ; ) abgeschlossen werden?

3

Warum eigentlich irgendwelche Custom Integrationen verwenden?

Wobei ich gar nicht weiss, ob du innerhalb einer SQL Abfrage ein Template einsetzen kannst.

Mit Home Assistant Hausmitteln, würde ich die Aktion recorder.get_statistics verwenden.

Doku siehe hier:

Anstatt eines input_select für die Datumseingabe, würde ich einen input_datetime verwenden.

Ein Template Sensor, der sich z.B. jeden Tag um 00:01 aktualisiert, würde dann so ausschauen:

template:
  - triggers:
      - trigger: time
        at: "00:01"
    actions:
      - action: recorder.get_statistics
        data:
          start_time: "{{ states('input_datetime.datum_jahresabrechnung_gas') }}"
          end_time: |
            {% set datum = as_datetime(states('input_datetime.datum_jahresabrechnung_gas')) %}
            {{ datum.replace(year=datum.year + 1) }}
          period: day
          statistic_ids:
            - sensor.gasverbrauch_kwh
          types:
            - change
        response_variable: stats
    sensor:
      - name: Jahresabrechnung Gas
        device_class: energy
        unit_of_measurement: kWh
        unique_id: 46cbdaf6-de3c-471b-997b-db089039b9b3
        state: |
          {% set total = namespace(sum=0) %}
          {% for item in stats.statistics['sensor.gasverbrauch_kwh'] %}
          {% if 'change' in item %}
          {% set total.sum = total.sum + item.change %}
          {% endif %}
          {% endfor %}
          {{ total.sum | round(3) }}

Das Enddatum end_time brauchst Du ja eigentlich nicht angeben. Wenn nichts angegeben, dann wird bis jetzt aufsummiert.

Gruß Osorkon

4

Hat leider nicht funktioniert. Der recorder liefert Daten, jedoch dürfte die Zeichenanzahl der limitierende Faktor beim Sensor sein. Selbst bei 14 Tagen werden Daten mit mehr als 255 Zeichen generiert. Schade.

Grüße Michael

5

Also bei funktioniert es selbst für ein komplettes Jahr und länger.
Irgendwas machst Du falsch, der Sensor Wert hat ja nur wenige Zeichen.

Las doch mal deinen Template Sensor sehen.
Wenn Du Dir den input_datetime.datum_jahresabrechnung_gas
Erstellen tust und diesen für die Datumseingabe verwenden tust, kannst Du mein Template 1:1 übernehmen. Die Zeit als Auslöser für den Test anpassen, das Du nicht erst bis 00:01 warten musst, bis der Sensor einen Wert liefert.

EDIT:

Wenn der Verbrauch ein stetig ansteigender Wert ist. Reicht es auch aus, der ersten und den Letzen Wert zu ermitteln und von einander abzuziehen.

Der Statistik Typ wäre dann state anstatt change

template:
  - triggers:
      - trigger: time
        at: "00:01"
    actions:
      - action: recorder.get_statistics
        data:
          start_time: "{{ states('input_datetime.datum_jahresabrechnung_gas') }}"
          end_time: |
            {% set datum = as_datetime(states('input_datetime.datum_jahresabrechnung_gas')) %}
            {{ datum.replace(year=datum.year + 1) }}
          period: day
          statistic_ids:
            - sensor.gasverbrauch_kwh
          types:
            - change
        response_variable: stats
    sensor:
      - name: Jahresabrechnung Gas
        device_class: energy
        unit_of_measurement: kWh
        unique_id: 46cbdaf6-de3c-471b-997b-db089039b9b3
        state: |
          {% set anfang = stats.statistics['sensor.gasverbrauch_kwh'][0].state | float %}
          {% set ende = stats.statistics['sensor.gasverbrauch_kwh'][-1].state | float %}
          {{ (ende - anfang) | round(3) }}

Statistik Auswertung

Gruß Osorkon

6

Lieber Osokorn,

du hast natürlich Recht. Jetzt funktioniert es auch bei mir. Würdest du mir noch verraten, wie du das Berechnen der Statistik mit dem Button = “Drücken” realisiert hast?

LG, Michael

7

Kann ich gerne tun, habe ich bereits an einer anderen Stelle dokumentiert. :grin:

Gruß Osorkon