I’m trying to build a sensor value to measure the last 2 days of precipitation history to use in managing my sprinkler automations. I’m using a WeatherFlow sensor that reports the precipitation yesterday but I’ve found it may update more than once a day. I’ve tried using the Statistics sensor which works but it will double count if the value is reported twice in the same day. For example, yesterday there was .05 rain but it was reported once at 12:15am and again at 9:30pm; therefore my sensor value is reporting a state of .1 because of the duplicates.
I know I could use some helper fields to capture historical data and a template to sum those but I feel this should be possible using Statistics. I don’t think History Stats will work since my state is a changing numeric value, not a string state to measure against.
Here’s my statistics code that works, but counts duplicates.
- platform: statistics
name: "Precipitation History Last 2 Days"
entity_id: sensor.weatherflow_precipitation_yesterday_rain_checked
state_characteristic: sum
max_age:
days: 2
I had the same request, and found this quetion which is quite old without any answer. Since I found a solution I put it here hoping it can help others.
My solution is to create a new entity which is the result of a SQL query on HA dataset
Here is the query I use for the cumulated value of the last 7 days :
select
sum(mm) as state
from
(
SELECT
date(
states.last_updated_ts, 'unixepoch'
) as date,
max(
cast(states.state as float)
) as mm
FROM
states
LEFT JOIN state_attributes ON (
states.attributes_id = state_attributes.attributes_id
)
WHERE
states.last_updated_ts >= strftime('%s', 'now', '-7 days')
and metadata_id = (
SELECT
metadata_id
FROM
states_meta
where
entity_id = 'sensor.pws_daily_rain'
)
group by
date(
states.last_updated_ts, 'unixepoch'
)
ORDER BY
state_id DESC
) as t
In this query my reference sensor is sensor.pws_daily_rain
