How can I drill down to an SQL query from a Sensor?

Configuration
1

I have a sensor called sensor.smart_meter_electricity_import_unit_rate.
I wish to create a query that will print out the state and last changed/updated as far back as possible.
I have very little SQL programming experience and trying to work out which tables I need to look in, is defeating me!
Regards, Martin

2

The database schema changes occasionally, so if you’re using old posts for inspiration, prepare for disappointment.

1 Like
3

Thanks for the warning!
This is the query that I working with so far:

SELECT state,  DATETIME(last_updated_ts, 'auto')
FROM "states"
WHERE "metadata_id" = (select metadata_id from states_meta where entity_id = 'sensor.smart_meter_electricity_import_unit_rate') 
ORDER BY DATETIME(last_updated_ts, 'auto')

Any suggestions on why it only gives results back to the beginning of the month?
Regards, M.

4

10 days default storage.