How can I calculate my daily kW/h within an Automation?

Configuration
1

This is my proto Automation yaml code:

service: notify.myFileNotify
data_template:
  message: >-
    {{now().strftime('%Y/%m/%d-%H:%M:%S')}},,
    {{-states('sensor.electricity')}},,
    {{-states('sensor.esp32_sdm230m_import_active_energy')}},,
    {{-states('sensor.givtcp_sa2219g385_import_energy_total_kwh')}}

I would now (for each of the three sensors) like to calculate the difference between now and 24hrs ago.
Pointers please. Do I look to the SQL, History or Recorder integrations to get yesterdays state?

Regards, Martin

2

sql easiest (for me), other option is to daily store value in a helper
EDIT: if it is only to make it visble then you can also use a graph, e.g. apexcharts allow you to compare to day values with an offset. This is based on ‘history’ so only available for as long as you have set this for your recorder
image

3

Hi,
Thank you for the pointer. Unfortunately, I have not programmed in SQL for many (30’ish) years!
This is as far as I have got with my SQL sensor integration:

SELECT * FROM states WHERE entity_id = 'sensor.electricity' ;

Any pointer at finding the state at (now-24hrs)?

Regards, m.

4

I have no access to my db now but in general you should first write your SQL directly there as trial/error via the SQL sensor is ‘hell’.
Your data should be in ‘statistics’ table and it should be something alike this for mariadb (sqlite will be different on the date selection)

select state from statistics where metadata_id = (select id from statististics_meta where entity_id = 'sensor.electricity') and created > now() - INTERVAL 23 hour and created < now() - INTERVAL 24 hour

which will get you any value created between 24 and 23 h ago…this should be 1 value

5

SQL will always and only kick back 1 value, if the sql provides a list only the first will be kicked back

6

Sorry to keep pestering… But taking your suggestion, I can get all the states for the ‘sensor.electricity’ with this truncated query:

select state from statistics
   where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.electricity') ;

So… I’m still stuck trying to filter on time. In earlier overs I talked about “NOW() minus 24hrs”.
But as my automation runs at midnight, would it be easer, to just locate the sensor state with a timestamp of midnight? How could I do that?

Regards, M.

7

How about using derivative.

I use it to calulate the active power averaged over 1 minute, it should work for 24h as well.
You could then get a value at a fixed time every 24h.

- platform: derivative
  source: sensor.current_summation
  name: current_summation_derived
  round: 3
  unit_time: h
  unit: kW
  time_window: "00:01:00"
8

I have no clue what db you have… for mariadb I gave you the options …are you on SQLite?
If so, did you try to search for it on this forum and try it out with a db tool (e.g dbbrowser for sqlite)?

Try this:

select state from statistics
   where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.electricity') 
  and date > datetime(datetime(), '-24 hour') and date <  datetime(datetime(), '-23 hour')

you’d have to play around a bit with the 24 / 23 to see if it is what you want…hence browsing the db with a tool is sort of needed.

9

It is not a fixed time the OP wants…but .‘now’ -1d … to het a figure out, I know no other option than SQL which does not mean there are none of course. HIst Graph wise this is easy, but single values … not

10

Thank you all very much for your help. I’m sorry that I was not very precise with my questions.
But you did give the pointers I needed and eventually, I think, I have got what I need.

select state from statistics where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.electricity') 
   and time(created) >= '00:00:00' 
   and time(created) <= '00:01:00'
   order by created desc
   limit 1

I’m sure the above can be improved upon but its good enough to allow me to move forward.

Thank you all once again. M.

11

The derivative is computed “continuously” - so by using the current value of the derivative, he should and a time_window of 24 hours and a unit_time set to “d”, he should get the “now” daily average in that sensor.

12

I use the “Utility Meters” integration in my configuration.yaml file to create daily, weekly, monthly & yearly totals for many things like my Solar production, Gas & Electricity usage, GivEnergy Battery IN & OUT etc etc

13

Thanks Graham,
That looks useful. I’ll experiment.
Regards, M.