SQL integration and long term statistics

Configuration
1

I have a zigbee temperature sensor out in may garden, and have created a “yesterday’s temperature” sensor using the SQL sensor and the example from that sensor’s documentation. Specifically the SQL query is:

select states.state from states inner join states_meta on states.metadata_id = states_meta.metadata_id where states_meta.entity_id = ‘sensor.garden_temp_temperature’ and last_updated_ts <= strftime(‘%s’, ‘now’, ‘-1 day’) order by last_updated_ts desc limit 1;

It has occurred to me that it might be interesting to also have a “Last year’s temperature”. Of course that means delving into HA’s long term statistics so the above query won’t work. I’m sure given enough time I could work out the appropriate query but has anyone done this already and could point me the direction of a long term statistics equivalent of the above? If not, I’ll follow-up here with it when I get it working.

Thanks,
Steve.

2

In a nutshell:

select state from statistics inner join statistics_meta on statistics.metadata_id = statistics_meta.metadata_id where statistics_meta.statistics_id = ‘sensor.garden_temp_temperature’ and statistics.start_ts <= strftime(‘%s’, ‘now’, ‘-1 y’) order by statistics.start_ts desc limit 1;

so no big difference.

The important thing to keep in mind is that, depending on the state_class of the entity, you would use “min/max/mean” (for “measurement”) or “state/sum” (for “total*”)

So for a temperature, you’ll probably do a select mean from ... actually.

1 Like
3

Perfect. Thank you. That looks like it works nicely. It was the structure of the statistics tables I was missing.

Thanks,
Steve.

4

what is the final code or sensor to have the temperature -365days of a sensor (in my case: sensor.cestas_temperature)?

5

This is my code:

SELECT mean FROM statistics join statistics_meta on statistics.metadata_id = statistics_meta.id 
where statistics_meta.statistic_id = 'sensor.garden_temp_temperature'
and start_ts <= strftime('%s', 'now', '-1 year') 
 order by start_ts desc limit 1

It looks back 1 year, which isn’t quite the same as 365 days, but just change the “-1 year” to suit. I have a similar sensor looking back 1 day for yesterday’s temperature.