The states() function has access to the data in the state object, not the entire database. To access past data you need to set up a SQL sensor. There is an example for previous data that may help you structure the query for your sensor.
with the example of database space, it works fine. this confirm i can obtain data of my mariadb database.
But its imposible to me create a sensor for my gas meter, or a temperature sensor. I try with some sensors and allways the same error “SQL error”
I use the sql for the example and only change the sensor name.
My understanding is that MariaDB requires a different query than the default SQLite database, so you will need to tailor most of what is presented in the HA SQL docs. I am not a SQL expert but based on this thread you likely need to use something like the following:
# column: "state"
WHERE metadata_id=(SELECT metadata_id
AND last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR)
ORDER BY last_updated_ts DESC LIMIT 1;