antoonhuiskens
commented
5 months ago Here's an example postgres/timescaledb query for grafana. Thought I'd be verbose and comment on the value for me. my environment looks like this:
- postgresql 15.6
- timescaledb 2.14.2+extensions
- grafana 10.3
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. The other big aspect is the compression. I've been storing both home assistant and ltss in pg, and though the homassistant db only stores min/mean/max, it's currently floating at 178GB compared to the ltss DB at 76GB.
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:
- if you know the name of the sensor just use
WHERE entity_id = 'sensor.x' - Filtering for the right entities is done in the WHERE section,
- take care to extract numeric values: so use ::numeric in the SELECT, and filter for non-numeric in the WHERE.
- most of the query (until the WHERE section roughly) is boilerplate and typically wouldn't need much change.
With a timeseries visualisation, you'll need to take the following into consideration:
- set the "query format" to "timeseries"
- I typically use a "rename fields by regex" on the "transform data" tab to clean up the names of the series to show up in the legend.
- I configure the Unit for the timeseries manually, though you could use a config from query transformation to set the unit (but that doesn't work with "timeseries" queries for me)
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
Maxtaager
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 !