[SOLVED]Calculate sensor Increment / Difference in set time intervals (1h, 6h, 12h, 24h)

Configuration
2

Here is how I did it by querying the database (mariaDB):

  - platform: sql
    db_url: !secret hassioslave_db_link 
    queries:
      - name: history_1h
        query: SELECT * FROM states WHERE entity_id = 'sensor.my_sensor' AND created < DATE_ADD(NOW(), INTERVAL -3-1 HOUR) ORDER BY state_id DESC LIMIT 1;
        column: 'state'

db_url: !secret hassioslave_db_link : I have an external DB, so i needed to declare the link, if you are on default recorder settings i think this can be ommited.

created < DATE_ADD(NOW(), INTERVAL -3-1 HOUR) selects everything that was created over an hour ago. The -3 is there because i noticed my DB had wrong timestamps (offset by 3h) which i do not know - cannot bother to fix atm. So if your DB is on the right time, just -1 (or how many hours offset you need) should work.

ORDER BY state_id DESC LIMIT 1 Orders result by state_id (which is chronological) and selects the first, which should be the newest one.

Now that I have the value of 1h ago, I created a template sensor that substracts this, from the current and gives me the difference.

BTW: you can use a small app called DBeaver to test your queries.

Hope this helps someone,
Comments are always welcome, as I have little clue on what im doing :smiley: