Entity state from past, timestamp from another entity

Configuration
1

I would like to have my humidy sensors state from past, lets say 10min before exhaust fan turned on, so that i could refer to that value and shutdown exhaust fan when humidy is returned to “normal”.

I can get timestamp with this {{ states.switch.waveshare_rele_6.last_changed }}
This is sensor that i would like to get value from that timestamp sensor.bthome_sensor_51c5_humidity

I read about using SQL database, and i got so far that i get value from that sensor, but how i can get that -10 minutes??

  - name: Suihkun kosteus ennen poistoa
    query: >
        SELECT
          states.state
        FROM
          states
          INNER JOIN states_meta ON
            states.metadata_id = states_meta.metadata_id
        WHERE
          states_meta.entity_id = 'sensor.bthome_sensor_51c5_humidity'
          AND last_updated_ts <= strftime('states.switch.waveshare_rele_6.last_changed - timedelta(minutes=5)')
        ORDER BY
          last_updated_ts DESC
        LIMIT
          1;
    column: "state"

But this does not work, i get latest value instead of from that timestamp
How should i change my query??

2

I had some progress, but cant get two messages together, it shows unknown
With this query i can get humidy -5 min ago.

SELECT
          states.state
        FROM
          states
          INNER JOIN states_meta ON
            states.metadata_id = states_meta.metadata_id
        WHERE
          states_meta.entity_id = 'sensor.bthome_sensor_51c5_humidity'
          AND last_updated_ts <= strftime('%s', 'now', '-5 minute')
        ORDER BY
          last_updated_ts DESC
        LIMIT
          1;

And this is to get timestamp from another entity, to be used

SELECT
      last_updated_ts
FROM
  states
WHERE
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      states_meta.entity_id = 'switch.waveshare_rele_6'
	)
    ORDER BY
      last_updated_ts DESC
    LIMIT
      1

Tried to put second query inside strftime

SELECT
          states.state
        FROM
          states
          INNER JOIN states_meta ON
            states.metadata_id = states_meta.metadata_id
        WHERE
          states_meta.entity_id = 'sensor.bthome_sensor_51c5_humidity'
          AND last_updated_ts <= strftime('%s',(SELECT
      last_updated_ts
FROM
  states
WHERE
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      states_meta.entity_id = 'switch.waveshare_rele_6'
	)
    ORDER BY
      last_updated_ts DESC
    LIMIT
      1), '-5 minute')
        ORDER BY
          last_updated_ts DESC
        LIMIT
          1;

edit.
Found right query, replaced strftime with timestamp query and subtracted 5 min (300s)

SELECT
          states.state
        FROM
          states
          INNER JOIN states_meta ON
            states.metadata_id = states_meta.metadata_id
        WHERE
          states_meta.entity_id = 'sensor.bthome_sensor_51c5_humidity'
          AND last_updated_ts <= (SELECT
      last_updated_ts
    FROM
      states
      INNER JOIN states_meta ON
        states.metadata_id = states_meta.metadata_id
    WHERE
      states_meta.entity_id = 'switch.waveshare_rele_6'
    ORDER BY
      last_updated_ts DESC
    LIMIT
      1) - 300
        ORDER BY
          last_updated_ts DESC
        LIMIT
          1;
3

A simple approach could be to create an average sensor that averages the humidity over the last 2 hours.

Or use a statistics sensor and have it record the minimum

There’s also a blueprint available that does this.

4

I tried that blueprint, it didnt work when i used sauna, it started exhaust fan but never stopped.
Problem is that humidy doesnt fall fast enought when sauna is in use, to use derivate sensor.