Mariadb query for sql sensor possible?

Installation Home Assistant OS
1

hello dear smart home lovers,

I’ve been trying to get a mariadb query up and running as a sensor for a long time now.

before I try it out here much longer, is it possible to use this sql query as a HA sql sensor?

in phpmyadmin the query brings the targeted results, it just doesn’t work as a sensor.


		SELECT SUM(((UNIX_TIMESTAMP(p.last_updated) - UNIX_TIMESTAMP(h.last_updated)) /60)) as sum FROM states p INNER JOIN states h ON h.state_id = p.old_state_id WHERE p.state='idle' and h.state='heating'

Of course I looked at the topics dealing with mariadb queries, but I didn’t really get any smarter from them.

Thanks in advance :hugs:

2

Not that it really answers your question, but why not use the history_stats platform? The only real difference is your sql is getting one total if more than one entity can have a state of ‘heating’.

3

If sql works in phpmyadmin then just add ”sql” in HA settings - devices and services - add integration.

4

Seems like there must be something wrong with the sql-integration. I have a sql-query that works from command line, but I have never managed to get this working in HA UI. My line is:

SELECT avg(state) FROM (SELECT state FROM states WHERE entity_id ="sensor.timesforbruk" AND old_state_id > "0" AND state > "0" and YEAR(last_updated) = YEAR(CURRENT_DATE()) AND MONTH(last_updated) = MONTH(CURRENT_DATE()) ORDER BY state DESC LIMIT 3) two_entity;
5

DId you figure this out? I also have a more advanced SQL query that won’t allow me to add it using the integration. I think the problem is I am using variables to store a state from a while ago to compare it to the current value.