I want to get all previous states reported by a sensor displayed in frontend

Configuration
1

Hi
I want to get all previous states reported by a sensor displayed in the frontend.

My data is captured through a JSON string and put in a sensor.

mqtt:
  - sensor:
      - name: "Garden Birds"
        state_topic: "birdpi/json"
        json_attributes_topic: "birdpi/json"
        value_template: "{{ value_json.Common_Name }}"

I want to display these names of birds on the frontend with the total count of the specific bird ‘detected’ on the current day. (from Birdnet-Pi)

At the moment i’m displaying the latest bird detection. But as I said, I want a list of the birds of the current day and their total count that day.

I’m testing with Templates, but i’m stuck. I have this:

{% set entity_id = 'sensor.garden_birds' %}
{% set state_objects = states[entity_id] %}
{% set history_states = state_objects | selectattr('last_changed') | list %}

Historical States of {{ entity_id }}:
{% for state in history_states %}
  - Vogel: {{ state.state }}
{% endfor %}

With this Template, I get the error ‘KeyError:’. As of there are no previous states. But that not true. There are lots…

history_birds
history_birds1575×398 37.7 KB

Can someone help me with this?

Kind regards
Maarten.

2

You can’t get history from a template. You’d have to use an SQL sensor to generate a list of states over the past day.

3

I’m trying to get the history from a sensor, into a Template.

4

ok, please read what I wrote then.

1 Like
5

Yes, And you wrote that I’m trying to get History from a template. But I’m trying to get it from a sensor.

That aside.

I’m using MariaDB, will the SQL sensor work like the regular db?

6

Yes and you’re miss understanding what I’m saying. A Jinja template ({{ }} {% %}) cannot get history from a sensor or anything.

7

Please look in the documents I linked, they have examples at the bottom covering the database types.

1 Like
8

I understand. I got the information from different topic online. Maybe that was a mistake. I’ll try to configure an sql sensor.

How can I visualize this data after the sql sensor is configured?

9

The sensor itself will have a state. That’s what you’d use. At that point, you can do whatever you want with the data. But because you want a list of states, you’ll most likely need to use a template to parse the data in a meaningful way. First step is to get the data though before you can get to that point.

10

True that! Thanks!

11

after you get the sql sensor working, post what you have configured. Then I can help with making it display better

12

I got the sql working. In phpMyAdmin and the SQL integration. I’ve put it in a sensor.

SELECT state FROM states INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id WHERE states_meta.entity_id = ‘sensor.garden_birds’ AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) ORDER BY last_updated_ts DESC;

The phpMyAdmin:

SQL1
SQL11762×801 36.3 KB

The SQL integration:

SQL2
SQL2597×990 34.9 KB

SQL3
SQL2
SQL2597×990 34.9 KB

You said that I can’t get the history through a template? How do I go from here?

This template gives only one entry, the last one.
{{states(‘sensor.vogels_van_de_dag’) }}

Kind regards and thanks for the knowledge!
Maarten.

13

Solved it. First I had a problem that the sensor was updating to unknown. This was due to the fact an sensor state can only have max. 255 characters.

I solved it with a HACS integration called sql_json.

And than in de configuration yalm:

sensor:
  - platform: sql_json
    scan_interval: 86400
    db_url: mysql://user:pass@core-mariadb/homeassistant?charset=utf8mb4
    queries:
      - name: "Vogelranglijst"
        query: >-
          SELECT CONCAT('[', GROUP_CONCAT(vogel_json), ']') AS json
          FROM (
            WITH states AS (
                SELECT state, COUNT(*) AS vogel_count
              FROM (
                  SELECT state FROM states
                  INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id 
                  WHERE states_meta.entity_id = 'sensor.garden_birds' 
                  AND last_updated_ts >= UNIX_TIMESTAMP(CURDATE())
                  ) AS states
                GROUP BY state
                ORDER BY vogel_count DESC
            )
            SELECT JSON_OBJECT(
                'state', state,
                'count', vogel_count
            ) AS vogel_json
            FROM states
            /*LIMIT 10*/
          ) AS json_output;
        value_template: '{{ value_json[0].count }}'
        unit_of_measurement: vogels
        column: json

After that I used the example in de documentation to form a front-end flex table card:

  - type: custom:flex-table-card
    columns:
      - data: json
        modify: x.state
        name: Vogels
      - data: json
        modify: x.count
        name: vogels/dag
    entities:
      include: sensor.vogelranglijst

The end result:

image
image403×1218 121 KB