Description:
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.
Query Used:
The SQL query being used is as follows:
sqlCopy code
SELECT
date_trunc('week', date_time) as time,
EXTRACT(isodow FROM date_time) as metric,
SUM(hh_kwhconsumption) as value
FROM energydatadbmodel
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.
Issue:
Despite aligning the query to the expected format (time, metric, value), both the metric and value are being displayed on the Y-axis in the heatmap visualization, which is not the desired outcome.
Steps Taken:
- 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.
Seeking Assistance:
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.
