In my homeassistant setup, I want to show all times where noone is home. So I was thinking about running the following query:
SELECT max("max_value") FROM (SELECT max("value") AS "max_value" FROM "homeassistant"."autogen"."state" WHERE time > :dashboardTime: AND ("entity_id"='galaxy_s8_john' OR "entity_id"='galaxy_s8') GROUP BY time(1m), "entity_id" FILL(previous)) GROUP BY time(1m)
As the state ist either 1 or 0, i thought this would first create a group for each minute and each phone, and then select the maximum value of these groups for each minute. But this does not seem to work, and the value just stays 0 and at sometimes jumps to 1 for a short time.
Why is my query not returning the expected result? And what would the correct query look like?
Thx in advance
Hi @johnzielke welcome ,
I assume that the state = 0 if nobody is home ?
I think following select gives the result you want ?
After replacing homeassistant by “homeassistant”.“autogen”.“state”,
I used homeassistant for my measurement name.
I am not sure about the FILLL(0) …
SELECT mva from (select max(value) as mva FROM homeassistant WHERE time > now() -20m and (“entity_id”=‘galaxy_s8_john’ OR “entity_id”=‘galaxy_s8’) and time > now() -1h GROUP BY time(1m), “entity_id” FILL(0)) where mva =0
You see in the second result that at 10:15 and 10:20 somebody was home
because these times are not in the list.
> select * from homeassistant
name: homeassistant
time entity_id value
---- --------- -----
2019-04-05T10:15:00.000000001Z galaxy_s8 1
2019-04-05T10:16:01Z galaxy_s8 0
2019-04-05T10:16:02Z galaxy_s8 0
2019-04-05T10:16:40Z galaxy_s8 0
2019-04-05T10:18:20Z galaxy_s8 0
2019-04-05T10:18:20.000000001Z galaxy_s8 0
2019-04-05T10:20:00.000000001Z galaxy_s8 1
> SELECT mva from (select max(value) as mva FROM homeassistant WHERE time > now() -20m and ("entity_id"='galaxy_s8_john' OR "entity_id"='galaxy_s8') and time > now() -1h GROUP BY time(1m), "entity_id" FILL(0)) where mva =0
name: homeassistant
time mva
---- ---
2019-04-05T10:11:00Z 0
2019-04-05T10:12:00Z 0
2019-04-05T10:13:00Z 0
2019-04-05T10:14:00Z 0
2019-04-05T10:16:00Z 0
2019-04-05T10:17:00Z 0
2019-04-05T10:18:00Z 0
2019-04-05T10:19:00Z 0
2019-04-05T10:21:00Z 0
2019-04-05T10:22:00Z 0
2019-04-05T10:23:00Z 0
2019-04-05T10:24:00Z 0
2019-04-05T10:25:00Z 0
2019-04-05T10:26:00Z 0
2019-04-05T10:27:00Z 0
2019-04-05T10:28:00Z 0
2019-04-05T10:29:00Z 0
2019-04-05T10:30:00Z 0
2019-04-05T10:31:00Z 0
