Please add additional examples #146
Comments
|
Here's an example postgres/timescaledb query for grafana. Thought I'd be verbose and comment on the value for me.
The data is fed from homeassistant to postgresql using the custom_component ltss from HACS My impression is that using timescaledb+postgresql, calculations are done much more efficiently and you have a lot more control over how many datapoints get sent back to grafana (if you're not careful, you easily see grafana complaining about limiting the amount of rows). It's pretty easy to use asap_smooth on 1 minute interval data for a year and downsample to say 3000 datapoints. If you leave that to pure grafana and postgresql... you can likely set fire to your computer and wait til kingdom come before a truncated set of data is returned. For now, I'm just using timescaledb enabled queries such as the one below (using stats_agg below, but also counter_agg, gauge_agg and asap_smooth) and this performs quite nicely even on an intel NUC i3. Most of the tricks are in figuring out what operation you want to apply, what the kind of sensor is (gauge, counter), pick the right aggregation function and apply the calculation. The timescaledb documentation (API-> hyperfunctions) is pretty good though. Once I get to the point that I'm not satisfied with the performance anymore, I'll start and create a couple of continuous aggregates for the time_bucket windows I require. This particular SQL-query collects sensor data from multiple sensors. As a couple of general directions:
With a timeseries visualisation, you'll need to take the following into consideration:
For easier troubleshooting: I tend to use grafana's query inspector to show the macro-expanded query and copy/paste it into pgadmin's query editor and run it separately. SELECT
-- I'm using Grafana's $__interval variable single-quoted as ${__interval} to pass the used interval.
-- Optionally: Adjust the min interval inQuery Options to something relevant (like 1h)
-- Alternatively, define a variable on the dashboard to set the bucket_interval and use ${bucket_interval}
-- Note that grafana query type "timeseries" needs a "time" column
time_bucket('${__interval}'::interval, time) AS time,
entity_id as entity_id,
-- Using timescaledb's stats_agg and average functions, a rounded average over the $__interval window
round( average( stats_agg( state::numeric))::numeric,1) as temperature
FROM
ltss
WHERE
-- $__timeFilter(time) is a grafana macro and creates a statement like "time BETWEEN timestamp1 AND timestamp2"
$__timeFilter(time)
-- From here, the query gets specific to filter on the sensors that are relevant.
-- Since we're interested in temperature, we filter for entities that report °C
AND attributes ->> 'unit_of_measurement' = '°C'
-- We exclude the -max and -min sensors, since they aren't gauge-like sensors, but report a daily/weekly/monthly min/max
AND entity_id !~ '.*max$'
AND entity_id !~ '.*min$'
-- Limit to sensor.meteobridge entities
AND entity_id LIKE 'sensor.meteobridge%'
-- Filter out those states that aren't numeric (This regexp is tricky to get correct, and not guaranteed to be bugfree)
-- zero or one "-", followed by at least 1 digit, optionally a "." and more digits until the end.
AND state ~ '^-?[0-9]+\.?[0-9]*$'
GROUP BY
-- Use this for the group expression: these need to align with the items in the SELECT part.
-- Grafana suggests to use the ${__timeGroup(time)} macro but this sometimes appends a "AS time" which is perfectly fine in the SELECT, it (sometimes) gives me grief
-- but not in the GROUP BY: Hence, I'm using time_bucket.
time_bucket('${__interval}'::interval, time),
entity_id,
attributes
ORDER BY time ASC
|
|
If you want to use annotations, the state_timeline() hyperfunctions are really fun. SELECT
start_time,
end_time,
'home assistant status' as title,
state as text
FROM
state_timeline(
(
SELECT
state_agg(time, state)
FROM
ltss
WHERE
time > NOW() - '30d'::interval
AND entity_id = 'sensor.victron_system_input_source'
AND state <> 'unavailable'
)
)
WHERE state = 'NOT_CONNECTED'You'll have to create an annotation for each state you'll want to display. The table would look something like this: The end result (combined with graphs) should look something like: |
|
sample query of shown when and how long my windows are open/closed shown in a table in grafana.
|
It would be nice if you had some examples of using ltss stats in Grafana and perhaps in Lovelace using a graphing card... just might help illustrate the value . Love the project though !
The text was updated successfully, but these errors were encountered: