Help with MAX sensor value

Greetings,
i want to put a sensor MAX value for the last week for example
query: “select max(strftime(’%Y%m%d %H:%M’,created,‘7 day’)) as created, state FROM states WHERE entity_id = ‘proxmox_neo_cpu_temp’ and domain = ‘sensor’;”

Doesnt work, please help :slight_smile:
I want to put on the graph the max value that was during last 7 days.
Thank you

Where do you try to query that? Or is it just an example?

1 Like

Hi, yeah example. i have bunch of others that work but this one the query is wrong
This one is for sensor value 24hours before.Working correctly

query: “select max(strftime(’%Y%m%d %H:%M’,created,‘1 day’)) as created, state FROM states WHERE entity_id = ‘sensor.spotreba_denni_celkem’ and created between datetime(‘now’,’-1 DAY’,’-1 hour’) and datetime(‘now’,’-1 DAY’) and domain = ‘sensor’;”

So you do want to query it somewhere?

Other option would be to look at Custom:mini-graph-card.

1 Like

This is the solution but i have errors
card not found
Resource is there
/local/mini-graph-card-bundle.js
I have dual gauge card which is working.
So this minigraph whats the problem :smiley:

Are you using the SQL sensor integration or are you doing this outside of HA?

When you say “doesn’t work”, what happens? What error messages do you see?

If you are using the SQL sensor, please post your code here (see rule 11). Could be something as simple as a formatting error.

1 Like

@ukro Did you install it according to the instructions? (Easiest way, after initial setup, is via HACS)

Yeah, that’s what I’m trying to figure out…

1 Like

Hi,
Thank you for reply.
integration inside the configuration.yaml
Working:

  - platform: sql
    scan_interval: 60
    queries:
      - name: spotreba_minuly_den
        query: "select max(strftime('%Y%m%d %H:%M',created,'1 day')) as created, state FROM	states WHERE entity_id = 'sensor.spotreba_denni_celkem' and created between datetime('now','-1 DAY','-1 hour') and datetime('now','-1 DAY')	and domain = 'sensor';"
        column: 'state'
        unit_of_measurement: 'kW' 

but i cant figure out how to make MAX.
No error’s in log
I’m now trying to do the graph javascript, it looks nice :>

Thank you :slight_smile: working like a charm

type: 'custom:mini-graph-card'
entities:
  - entity: sensor.proxmox_neo_cpu_temp
    aggregate_func: max
    name: Max
    color: null
  - entity: sensor.proxmox_neo_cpu_temp
    aggregate_func: min
    name: Min
  - entity: sensor.proxmox_neo_cpu_temp
    aggregate_func: avg
    name: Avg
    color: green
name: Proxmox NEO temps
hours_to_show: 168
group_by: date

Screenshot_107

In case you wanted to continue with the SQL approach:

You seem to be looking for the maximum of the created column, which makes no sense. Have you created this yourself, or is this copy-pasted without any real understanding?

On the sqlite3 command line, this finds the highest value of my outside temperature sensor from the last seven days:

SELECT created, MAX(CAST(state AS float)) FROM states WHERE entity_id = 'sensor.outside_temperature' AND created BETWEEN datetime('now', '-7 days') AND datetime('now');

The CAST is important: the state is stored as a string, and without casting it to a float, the MAX function returns 9.9 instead of 17.2 as it’s alphabetically greater.

This should give you a sensor whose state is the maximum value, with the timestamp stored as an attribute.

Thank you,
i might want both approches
I just copy pasted :shushing_face: :smiley:

2021-04-30 10:16:24 ERROR (SyncWorker_4) [homeassistant.components.sql.sensor] Error executing query SELECT created, MAX(CAST(state AS float)) FROM states WHERE entity_id = 'sensor.proxmox_neo_cpu_temp' AND created BETWEEN datetime('now', '-7 days') AND datetime('now') LIMIT 1;: "Could not locate column in row for column 'state'"
2021-04-30 10:16:24 ERROR (Thread-3) [root] Uncaught thread exception
  - platform: command_line
    scan_interval: 60
    name: proxmox_neo_cpu_temp
    command: 'ssh -i /config/custom_components/ssh/id_rsa -o StrictHostKeyChecking=no -q [email protected] cat /sys/class/thermal/thermal_zone3/temp'
    value_template: "{{ value | int / 1000 }}"
    unit_of_measurement: "°C"

  - platform: sql
    scan_interval: 60
    queries:
      - name: proxmox_neo_cpu_temp_max
        query: "SELECT created, MAX(CAST(state AS float)) FROM states WHERE entity_id = 'sensor.proxmox_neo_cpu_temp' AND created BETWEEN datetime('now', '-7 days') AND datetime('now');"
        column: 'state'
        unit_of_measurement: '°C' 

Developer tool

 sensor.proxmox_neo_cpu_temp	37.0	unit_of_measurement: °C
friendly_name: proxmox_neo_cpu_temp

Ah, sorry. You need to add “AS state” after the CAST function to get the column header.

sqlite> .headers ON
sqlite> SELECT created, MAX(CAST(state AS float)) FROM states WHERE entity_id = 'sensor.outside_temperature' AND created BETWEEN datetime('now','-7 days','-1 hour') AND datetime('now');

created                   |MAX(CAST(state AS float))
2021-04-23 12:15:15.492119|17.1

sqlite> SELECT created, MAX(CAST(state AS float)) AS state FROM states WHERE entity_id = 'sensor.outside_temperature' AND created BETWEEN datetime('now','-7 days','-1 hour') AND datetime('now');

created                   |state
2021-04-23 12:15:15.492119|17.1

So your sensor should become:

  - platform: sql
    scan_interval: 60
    queries:
      - name: proxmox_neo_cpu_temp_max
        query: "SELECT created, MAX(CAST(state AS float)) AS state FROM states WHERE entity_id = 'sensor.proxmox_neo_cpu_temp' AND created BETWEEN datetime('now', '-7 days') AND datetime('now');"
        column: 'state'
        unit_of_measurement: '°C' 
1 Like

Perfect ! like a charm. Thank you very much !

1 Like