Hello Grafana Community,
I’m encountering an unusual issue with a heatmap visualization when trying to represent data fetched from a PostgreSQL database and hoping to find some assistance or insights here. The intention is to display weeks on the X-axis, weekdays on the Y-axis, and the color to represent total consumption.
The SQL query being used is as follows:
date_trunc('week', date_time) as time,
EXTRACT(isodow FROM date_time) as metric,
SUM(hh_kwhconsumption) as value
WHERE date_time IS NOT NULL AND hh_kwhconsumption IS NOT NULL
GROUP BY time, metric
ORDER BY time, metric;
time: Intended to map to the X-axis, showing week commencing dates.
metric: Intended to map to the Y-axis, showing weekdays (1-7).
value: Intended to represent the color scale on the heatmap, showing total consumption.
Despite aligning the query to the expected format (time, metric, value), both the
value are being displayed on the Y-axis in the heatmap visualization, which is not the desired outcome.
- Validated SQL Query: The query has been run directly against PostgreSQL to ensure it returns expected and non-null results.
- Checked Panel Settings: The Grafana panel is set to the “Time series” query format and is connected to the correct data source.
- Grafana Version: [Your Grafana Version] (Updating to the latest version may be applicable if not already done).
- Tried Alternating Visualization: Using a table visualization confirms that the data returned from the query appears as expected.
Given the described context, has anyone encountered a similar issue or could provide insight into potential misconfigurations or steps to troubleshoot this further?
I am reasonably new to using Grafana/SQL querying so any assistance or direction would be greatly appreciated. Thank you in advance!
Additional Note - Visually, I want to achieve something like the below.