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();
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' );
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!
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.
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')
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!
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 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;
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.
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;
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;
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.
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;
Just a small warning: deleting or even changing anything from sqlite causes database to crash (done that, not once before i figured this out).
Deleting/changing things in mariadb is ok, though.
regarding
AI is not a not a threat to humanity…
well… 10 or 15 years ago making a telephone call from hand device (smartwatch, as seen in star wars) was science fiction…
AI currently is definitely not a threat, because in reality it’s not even close to that “I” in the name. Thing just follows written program-script-whatever…, that’s all. Nothing intelligent here… only intelligent “thing” is programmer behind it. 40 (or so) years ago a simple calculator was ultimate AI, because it could calculate that 1+1 is in fact really 2
When these boxes will start to think and decide for theirselves beyond programmed THEN we’ll have to worry about terminator movie becoming real stuff.
But, enough offtopic.
