It seems like using the heatmap plugin for elasticsearch is super easy, but I’m trying to create an SQL query which will allow me to have it in PostgreSQL / TimescaleDB. According to this section it seems like it should be possible. Here is my attempt so far:
SELECT
time_bucket('1 hours', "read_at") AS time,
grouped_humidity,
count(*) AS count
FROM (
SELECT
read_at,
((humidity::integer / 3) * 3) AS grouped_humidity
FROM temperature_humidity
WHERE $__timeFilter(read_at) AND humidity IS NOT NULL
) grouped_humidities
GROUP BY time, grouped_humidity
ORDER BY time, grouped_humidity
Do I need to return an array with tuples of humidity/count perhaps? Like maybe using something like ARRAY_AGG? I’m not so familiar with ARRAY_AGG…
I tried this with format set to “Time Series” and it actually works for smaller values, but when I go to longer time spans grafana just crashes. For a 1 week span it returns something like 42,000 rows, so that might be why:
SELECT
read_at AS time,
((humidity::integer / 3) * 3) AS grouped_humidity
FROM temperature_humidity
WHERE $__timeFilter(read_at) AND humidity IS NOT NULL;
What does your query return (show in table view in panel inspector / Data). Elasticsearch has a histogram group by where it returns a time series for each bucket. Does your postgres query have a histogram group by as well (so each bucket is returned as a separate time series)?
Though I’m happy to query for data in a different way. It’s been a bit, but IIRC Grafana could handle this if each bucket of the metric (grouped by modulus 3) was a different column, though that would be a pretty ugly query and slow (I think, at least from when I tried before). Again IIRC it would be possible in PostgreSQL to group things up into an array in a single column, but then I think that Grafana didn’t support that. Does that seem right?
