SQL sensor not updating before the next day

Configuration
1

Hi people

I have an SQL query that I tested in phpmyadmin in it works like I want it to.
When I create an SQL sensor in Home Assistant it also “works”.

The problem is that this sensor says ‘Expired’ and ‘unknown’ in frontend until the next day.

image
image419×660 31.9 KB

My SQL query:
SELECT GROUP_CONCAT(state, ': ', Total SEPARATOR ‘–’) as Total FROM( select state,count(*) as Total 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()) group by state ORDER by Total DESC ) q;

Then, after 00:00am, the sensor suddenly changed:

image
image408×643 30.1 KB

Why is it that this SQL sensor is only updating after midnight?

The same happens when I manually add a SQL sensor in the configuration.yalm.

When I change the query, I have to wait untill then? That’s not very practical.
I think i’m forgetting something or don’t know something.

Hopefully someone can nudge me in the right direction.

Kind regards!
Maarten.

2

Ok. Replying to my own topic.

The problem seems to be that the string that is returned is larger than 255 characters.
And an sensor state can only be max. 255 characters.

Can I store an SQL query into an attribute? Because from what I’m reading in the community, attribute strings can be longer.

3

Solved it with sql_json through HACS.

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