Displaying a heatmap for TimescaleDB

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)?

Thanks! Here’s a sample of the data that’s returned:

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?

We’re using Grafana 8.1.2, by the way