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 
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?
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.
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 
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.
@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…
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 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
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 
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'
Perfect ! like a charm. Thank you very much !