#Compute percentage of time a condition was true, on a given period using LTS ?

26 messages · Page 1 of 1 (latest)

oblique acorn
#

Hi all
I'm trying to compute the average of time (percentage) a condition was TRUE using "long term stats".
I went into the following assumptions:

  • LTS only save numbers. => so i first created a helper to convert "False" to 0, and true to 100 ... great it shoudl be saved on LTS
  • LTS are aggregated, so the average computed by HA will be usefull in this case ... => One minute reported as "100" via the helper described above, will now add " 100/60" in the corresponging timeslice of 60 minues in LTS => perfect ( it will automatically add 1.6% of the corresponding LTS hour ).

now, it may sound stupid, but i would like to create 3 new sensors/templates.

  • average "on" (in %) during last 24h
  • average "on" (in %) during last 7 days
  • average "on" (in %) last 30 days.

which helper is using LTS ? That's not clear to me.
Can i just use a simple "mean" ?
What will happens if the condition is/was false for 5 hours ? do we have 5 LTS records at zero ? do i need to use "step mean"?

Thank you for any advices/best practices !
regards

void dew
#

LTS isn't actually accessible by anything, except just graphing it.

#

You can't use it in any further calculations.

#

Except maybe if you want to write raw SQL queries.

oblique acorn
#

Thx @void dew .That's a really bad news 🙂 🙂 🙂 🙂
Way more difficult than expected... ( as always 🙂 )

turbid narwhal
#

What you want is history stats which can do exactly this

lyric girderBOT
oblique acorn
#

I 'm testing it since yesterday ... and what confuse me about the "History Stats", is the field "State"... I went into the code and saw it was declared as text... Do we have texts now in "Long term stats"????...
Otherwise, it looked promising 😉 ...
I have provided value "100" ...and it works fine now, but as soon as values will be aggregated by "hour", the value may not be 100 anymore (between 0 and 100 🙂 ), and those records may just be ignored/skipped by the "History Stat" logic... we will see but i'm not confident at all ...

image.png
oblique acorn
#

may be the solution is to perform SQL statements...
but we ( I, me, yes sorry ... 😉 ) have to extrapolate the fist timeframe ( as LTS is saved per hour it may not match the analyzed period), do the same for the last one (same reason), and add full hours retreived in between ... So basically we have 3 selects, ...

turbid narwhal
#

Change the type to ratio and you get the percentage you are looking for directly

oblique acorn
#

I 'm already using "ratio"... and up to now it works fine ...

turbid narwhal
#

You can also use a statistics helper to do the same kind of thing - average_step on a binary sensor gives you the percentage of time it's on

turbid narwhal
oblique acorn
#

i have this ;

turbid narwhal
#

Seems like your statistics sensors disagree

oblique acorn
#

yes and 2 first ones are correct 😉

#

but as soon as the data will be retreived from the aggregated ( LTS) ... it will stops... as recorded values will not be 100 anymore ...

#

I mean if the battery is fully charged at 8.45 , the corresponding entry in LTS ( time slice from 8am to 9am) will have the average ...25!!!!! ( a quarter of an hour fully charged) ...

#

( we will see in 3 days...)

turbid narwhal
#

Just use the binary sensor directly...

void dew
#

History stats reads from the state table, not LTS.
Statistics reads from the state table, not LTS.

oblique acorn
#

I'm already tring to execute some SQL 🙂

oblique acorn
#

First attempt:

SELECT 
    public.statistics_meta.statistic_id , 
    count(*) ,
    ( sum(mean) / count(*) ) as avg_7days
FROM public.statistics
join public.statistics_meta on statistics_meta.id =  public.statistics.metadata_id 
where 
      public.statistics_meta.statistic_id = 'sensor.helper_battery_fully_charged' and
      to_timestamp(start_ts) >= clock_timestamp ( ) -  interval '168 hours' 
group by public.statistics_meta.statistic_id

execution time on postgresql: Total rows: 1 Query complete 00:00:00.076
It's already fast enough 😉