Displaying a heatmap for TimescaleDB

Grafana Support
#1

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…

#2

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;