#hmm that was an essential part really to
bleak thunder
Yeah, I do this with energy monitoring via GreenEye Energy Monitor
What do you mean "minus 24 hours"?
brave geyser
that's just how I was thinking of it in terms of the sql query
bleak thunder
So you want to show the same room today and yesterday? or all rooms, today only?
brave geyser
whenever the current value is being written to the graph, also draw that same row from the day before
the same room today and yesterday
bleak thunder
k, lemme think about this...I didn't realize you could use SQL in the frontend, that opens up some options.
bleak thunder
I bet you can do this with value_template
brave geyser
haven't come across that - Googling...
bleak thunder
You'd use it to create a sensor based on the value of another one....
So you might be able to create a sensor that is the previous 24 hours
brave geyser
I see! that sounds perfect
bleak thunder
not promising it can do that, but looking into it
Did you have to add an add-on for SQL, you mentioned doing something in the GUI.
brave geyser
I added it as an integration
bleak thunder
nm, found it, adding now
Did you create the sensor.average_house_temp or is it being provided by one of your entities?
brave geyser
it's a template sensor
sensor 13:
platform: template
average_house_temp:
friendly_name: "Average House Temp"
unit_of_measurement: "°C"
value_template: >-
{{
((
float(states.sensor.living_room_temperature.state) +
float(states.sensor.kitchen_temperature.state) +
float(states.sensor.studio_temperature_3.state) +
float(states.sensor.bedroom_ir_temperature.state) +
float(states.sensor.landing_temperature.state) +
float(states.sensor.kitchen_current_temperature.state)
) / 6) | round(1)
}}
Made any progress? I think I may have.
I tried my queries at the command line first.
This gives me the current value:
SELECT state FROM states WHERE entity_id = 'sensor.office_current_temperature' ORDER BY state_id DESC LIMIT 1;
while this gives the me the value exactly 24 hours ago:
SELECT state FROM states WHERE entity_id = 'sensor.office_current_temperature' ORDER BY ABS(strftime('%s', created) - strftime('%s', datetime('now'), '-1 day')) LIMIT 1;
And when I put either of those queries in the "Select query" portion of the configuration it also returns those results.
I don't know how that example has the values overlain on top of each other, mine won't do that. Also, the newly created, sql-sourced sensor doesn't have 24 hours worth of data in it.
Ah, they must share the same unit of measure for that to happen