SQL query to get historical data from an entity with state start and end timestamps in the same row/result

[ejalal]

Hey all,

I would like to create a mariadb view to get state start and end timestamps for a selection of entities but it’s a bit more complicated with the HA db structure. Something like the history graph but directly with sql. This view will server for future sql sensors later on.

Any one managed to do something like this ?

[Troon]

This might help?

UK electricity demand reduction calculator

EDIT: query updated for the v32 database schema introduced in 2023.2: the last_updated date-time has changed to last_updated_ts epoch timestamp. Here in the UK, the grid operator has introduced a demand flexibility service to incentivise reduction of electricity usage at peak times to avoid having to fire up the coal plants. My supplier, Octopus (sneaky referral link) has implemented this as Saving Sessions, where you get rewarded with points for reducing your energy use during one of these sl…

I try to explain what the query is doing in the post.

If you need more help, be more specific in what output you’re looking to achieve.