All entity_id NULL

[tom_l]

Yes see the examples in the link I posted above.

[quartstudio]

thanks it works!

[Protoncek (Pavel)]

Hm…as i’m not sql pro i don’t have a clue how to correct this to working version: (it’s mariadb database, in synology): sensor should show min (and max alternatively) temperature TODAY, so from midnight to right now.

- name: minimum outside temperature
  db_url: !secret database_url
  unit_of_measurement: °C
  column: "min_temp"
  query: >
    SELECT MIN(CAST(state AS DECIMAL(3, 1))) AS min_temp
    FROM states
    WHERE entity_id = 'sensor.outside_temperature'
    AND state IS NOT NULL
    AND state NOT IN ('unknown', 'unavailable')
    AND FROM_UNIXTIME(last_updated_ts) >= CURRENT_DATE();

[quartstudio]

try something like:

SELECT MIN(CAST(state AS DECIMAL(3, 1))) AS min_temp FROM states LEFT JOIN state_attributes ON (states.attributes_id = state_attributes.attributes_id) WHERE metadata_id = ( SELECT metadata_id FROM states_meta where entity_id = 'sensor.outside_temperature' );

[Protoncek (Pavel)]

Thanks!
now i approximately see the point… i must replace “where entity_id = ----” with another sql sentence, where i extract entity via metadata_id…
thanks again!

[Spartacus]

Hi all,

can someone please help me? I am not very experiance in SQL and I have round about 100SQL queries in Grafana and nothing works anymore.

Can someone please translate this example to the new format?

SELECT
  last_updated_ts AS "time",
  '°C' AS metric,
  CAST(state AS float) AS "InnenTemp"
FROM states
WHERE
  state <> 'unavailable' and
  state <> 'unknown' and
  entity_id = 'sensor.1w_ds2438t'
ORDER BY last_updated_ts

I never saw something like this, how is it possible to make so significant changes from one day to annother! I am very frustarated about this.

Thanks in advanced!
Spartacus

[quartstudio]

Try this:

SELECT
  last_updated_ts AS "time",
  '°C' AS metric,
  CAST(state AS float) AS "InnenTemp"
FROM states
WHERE
  state <> 'unavailable' and
  state <> 'unknown' and
  metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.1w_ds2438t')
ORDER BY last_updated_ts;

The point is, we should stop using entity_id (cause they are saving disk space, lol). Instead we can reference with metadata_id and with another query.

So instead of:

WHERE entity_id = 'sensor.your_sensor'

use:

WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.your_sensor')

[Spartacus]

Hi all,

many, many thanks for the very quick response. I also got it working! Now I have to change all the Grafana dashboard Querys. I do not understand, why people are changing a running system in such a significant way…but maybe I missed the announcements!

[maurcabezas (Mauricio Cabezas)]

same problem after update 2023.4

[maurcabezas (Mauricio Cabezas)]

Thank you, you are right, I had just simple change the queries and all right now, !

[tom_l]

@Spartacus

but maybe I missed the announcements!

Second last paragraph of this section of the release notes https://www.home-assistant.io/blog/2023/04/05/release-20234/#database-scalability

That section also explains why the changes were made.

[leonidostrovski]

Thank to all. I happy know it is not problem on my db.
But still have my original problem.
I have sensor flood problem that increase my db size to GIGABYTES in 1-2 days. As result the HA stop normal response.
In past i ran:
SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 60;
for detect what sensor did a problem, and manually delete all data from it.
What now ?
How detect what sensor have a lot of data???

I think many users in same problem:

How to keep your recorder database size under control

Hello all, Thank you @denilsonsa for this guide. I have read this thread many times but still have problems with my Database. I had an install of HA with final Database size of 53GB. I have decided to make a fresh install and restored my back up. After reinstall my Database grew 1.1GB in first day. So i followed your guide and identified main causers of this. Afterwards I have applied filter to my configuration.yaml and issued a purge+repack. Now my Database is growing slower but still like …

[KevinCo (Kevin Cordery)]

I had to modify some statements today to grab the data. Below is example of join of states with states_meta.

select states_meta.entity_id, state, last_updated_ts from homeassistant.states
inner join homeassistant.states_meta on states.metadata_id = states_meta.metadata_id
where states_meta.entity_id = ‘sensor.xxxxxx’
order by last_updated_ts desc;

[damusmf]

having a similar problem when using phpmyadmin. I have figured out the new syntax to find what i am looking for, but whenever i try to sort by state i just get this error message

and in the homeassistant logs themselves

Filtered a request with unsafe byte query string: 
/api/hassio_ingress/xxxxx/index.php?route=/sql&db=homeassistant&table=states&sql_query=SELECT+%2A+FROM+states+WHERE+metadata_id+%3D+%28SELECT+metadata_id+FROM+states_meta+WHERE+entity_id+%3D+%27sensor.basement_th_temp%27%29++%0AORDER+BY+%60states%60.%60state%60+ASC&sql_signature=xxxxxb&session_max_rows=25&is_browse_distinct=0&ajax_request=true&ajax_page_request=true&_nocache=xxxxx&token=xxxxx

I have some finnicky sensors that report bogus data every time i restart HA. they are the acurite 433mhz temp sensor, but every reboot they say they are outrageous numbers, 400+ degrees and it messes with the graphs, just become a habit to quick search in phpmyadmin and adjust them since i dont reboot all that regularly. I tried to figure out why they do it to begin with but couldn’t so this workaround has been fine but now its a pain with sorting not working. I can click through the pages and will eventually find it, but that is not nearly as friendly as just sorting the state column.

[jumacabo (Juancho)]

With the new database schema you could use the following queries to detect which are the entities consuming most of the space in the 2 most crowded tables states and statistics

States

SELECT
  states_meta.entity_id,
  count(*) cnt
FROM
  states
  LEFT JOIN states_meta ON (
    states.metadata_id = states_meta.metadata_id
  )
GROUP BY
  states_meta.entity_id
ORDER BY
  cnt DESC;

Statistics

SELECT
  statistics_meta.statistic_id,
  count(*) cnt
FROM
  statistics
  LEFT JOIN statistics_meta ON (
    statistics.metadata_id = statistics_meta.id
  )
GROUP BY
  statistics_meta.statistic_id
ORDER BY
  cnt DESC;

[leonidostrovski]

Thank you, @jumacabo. It looks like a solution.

[fyp_08 (Ashwin)]

Hi, I’m using Home Assistant to collect power consumption data for analysis with network devices as my final year project. Although it was working fine before, I’ve suddenly noticed that most of the columns in the states table now contain NULL values. I’m new to phpMyAdmin and SQL, and I need to extract the power consumption (W) and relative time instances. This information would be really helpful for advancing my project. Your responses would be invaluable.

[leonidostrovski]

It is due to chnge of db. It is ok. The names in other tables. Search google for additional information.

[fyp_08 (Ashwin)]

These are the databases currently in Home Assistant. Since I’m new to this, could you give me a reference point to rectify this issue?

[String-656]

This works, just change to “sensor.xxxx”

select states_meta.entity_id, state, last_updated_ts from harepository.states
inner join harepository.states_meta on states.metadata_id = states_meta.metadata_id
where states_meta.entity_id = "sensor.scale_1_percent"
order by last_updated_ts desc;