a way to choose an entity and the date from long statistics to make the good sql
request, and something like (d at x hour) - (d-1 at x hour) - (w at x d at x hour) - (w-1 at x d at x hour) - (m on 1st d at x hour) - (m-1 on 1st d at x hour) - (y on 1st d at x hour) - (y-1 on 1st d at x hour) and corresponding to local time
- name: hpsbl
column: "state"
query: >
SELECT
state, last_updated_ts AS Timestamp
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.em_maison_bbrhpjb'
AND Timestamp <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) - (strftime('%s', 'now') % 86400) +
CASE
WHEN strftime('%m', Timestamp) BETWEEN '04' AND '10' THEN 25200
ELSE 18000
END
UNION ALL
SELECT
state, created_ts AS Timestamp
FROM
statistics
INNER JOIN statistics_meta ON
statistics.metadata_id = statistics_meta.id
WHERE
statistic_id = 'sensor.em_maison_bbrhpjb'
AND Timestamp <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) - (strftime('%s', 'now') % 86400) +
CASE
WHEN strftime('%m', Timestamp) BETWEEN '04' AND '10' THEN 25200
ELSE 18000
END
ORDER BY Timestamp DESC
LIMIT
1;
to make more simple some difficult request like this one for last week on monday at 6h00 with french specificty for hour (winter/summer)
tx for all