Obtain data of a sensor now and one minute ago

1

Hello.
I obtain the data of a sensor now with this very easy templante:

{{ states('sensor.gas_medicion', now() ) }}

And i want to obtain the value of this sensor, one hour ago. i use this template but not woks :frowning:

{{ states('sensor.gas_medicion', now() - timedelta(hours=1)) }}

this template returns the same value as the first template and i dont know the problem

any idea?

thanks a lot!

2

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.

FYI, the now() argument you have included in {{ states('sensor.gas_medicion', now() ) }} is not listed as an acceptable arguments for the states() function. I’m kind of surprised you aren’t just getting an error from that template.

2 Likes
3

I do not think the states() call have a second parameter, so everything after the second ’ is ignored.

4

In addition to the entity ID, there are some valid secondary arguments for states():

Screenshot from 2023-11-23 11-43-57
Screenshot from 2023-11-23 11-43-57730×368 28.1 KB

5

Thanks a lot!!

this information is very usefull, chatgpt in this case dont have the correct information, jeje

I will try with the sql sensor.
Thanks again!!

6

ChatGPT should be avoided at all cost in relation to HA.

Its even typed into point 23 for the forum guide.

1 Like
7

Thanks for that clarification.
It just improved a few cards in my dashboards. :slight_smile:

8

hello again.
Im trying to use the sql example for this example:
SQL - Home Assistant (home-assistant.io)

And allways apears a error.

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” :frowning:

I use the sql for the example and only change the sensor name.
any idea? :frowning:

9

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"

SELECT states.state 
FROM states 
WHERE metadata_id=(SELECT metadata_id 
FROM states_meta 
WHERE entity_id='sensor.gas_medicion') 
  AND last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR) 
ORDER BY last_updated_ts DESC LIMIT 1;
1 Like
10

oh i dont think on it.
adapt the query for the mariadb. i thought its the same
very good point! i will try with this and testing,

thanks a lot!

EDIT: THIS QUERY WORKS PERFECT!!!
Thanks a lot