Display historical sensor values as separate entities in card

Configuration
1

Hello, I have a SQL sensor for daily average temperature calculation (at 7, at 14 and at 21 twice). I would like to display it as 3 entities (maybe whole week) in an entity card from the latest to the oldest like this:image

I am quite new to Home Assistant and only way I can achieve this is to create three template sensors, but I do not know how to change them the name dynamically, if there is any automation that can every night generate new names for static template entities or is there a better way to achive this.

Thanks

2

Again some form of template.

When doing the sql query you will have the date, express it as a day of the week.

3

Thank you for your response, but can you show me, what the word “some template” means?

SQL sensor seems to be able to return only one row and one column. So if I want to dispaly 3 days, do I have to have 6 SQL sensors? One for the “day” one for the value?

Isn’t there a better way to display multiple values of SQL sensors? How can I refresh the name in template sensor? Via automation and update_entity? This looks very ugly and I think, there should be a better way.

4

OK let’s take another approach. Today is Monday 2 November 2020. Therefore you want the historical temperatures at this time for Friday, Saturday and Sunday? Is that right.

What SQL query are you using for, say, Saturday’s temp?

5

Yes, you are right. For Saturdays’s temp i will use this sql:

hass=> SELECT * FROM history.archive WHERE tstamp > CURRENT_DATE - interval '1 day';
         tstamp         | value 
------------------------+-------
 2020-11-01 00:00:00+01 |   7.4
(1 row)

I’d like to display these rows:

hass=> SELECT tstamp, value, to_char(tstamp, 'Day') as dow 
FROM history.archive 
WHERE tstamp > CURRENT_DATE - interval '3 day' order by tstamp desc;
         tstamp         | value |    dow    
------------------------+-------+-----------
 2020-11-01 00:00:00+01 |   7.4 | Sunday   
 2020-10-31 00:00:00+01 |   9.4 | Saturday 
 2020-10-30 00:00:00+01 |   7.8 | Friday   
(3 rows)
6

I think you should do three SQL sensors. One for 1 day ago, one for 2 days ago and one for 3 days ago.

You can then use one of the custom cards that does templating, eg https://github.com/thomasloven/lovelace-template-entity-row

I am rubbish at date/time templating, but this thread is awesome. The EPIC Time Conversion and Manipulation Thread!

You want to template something like (now() - 3days) formatted as %A

(%A is weekday in full, eg Saturday)

1 Like
7

Except now() is deprecated because it evaluates every minute!

8

This spits out the day of the week 3 days ago (259200 seconds ago)

{{ (as_timestamp(now())-259200)|timestamp_custom('%A')}}

Gives the result Friday (today being Monday).

Having said that, I believe now() is deprecated. Someone will pop along with a better way :slight_smile:

9

Ok, I have a sensor value from sql sensor in entities card:

Screenshot from 2020-11-03 18-10-40
Screenshot from 2020-11-03 18-10-40578×615 26.1 KB

law_avg_temp is sql sensor, law_avg_tempt is template sensor.

Now, how can I set a name?

When I tried to use a template in a list of entities, it will display like you can see on an uploaded image.

type: entities
entities:
  - entity: sensor.law_avg_tempt
    name: "Den {{ state_attr('sensor.law_avg_temp', 'dow')  }}"
  - entity: sensor.law_avg_temp
title: Average temperature

When I use friendly_name in the template sensor, the result is the same. Templates in friendly_name are not executed/parsed.

So the question from my first post is: How can I change the entity name dynamically? Is it possible via automation calling some hidden method?

10

As I already said