I have a watering system which each watering cycle creates a state record each minute. The ‘state’ counts down to zero from the defined number of minutes it wants to water. So a four minute watering cycle generates 5 records, the last one showing a state of zero.
If I try to use a history card to graph the variation in use over days (it’s smart enough to vary watering time based on temp, weather and moisture) I just get 0 every time.
So using the sqlite add-in I have selected the data I would want to be able to graph.
SELECT min(substr(datetime(created,'localtime'),1,16)) as time ,substr(entity_id,8) as 'where',max(state) as mins
FROM "states"
where entity_id like '%watering_time%' and state <> 0
group by substr(datetime(created,'localtime'),1,12),entity_id
This produces data looking like this:
time where mins
2019-10-17 00:00 plants_north_and_south_watering_time 4
2019-10-17 02:00 side_area_watering_time 4
2019-10-17 22:00 side_area_watering_time 4
2019-10-18 00:00 plants_north_and_south_watering_time 3
2019-10-18 02:00 side_area_watering_time 3
How can I get this displayed in a card and graphed as well ?