Updated HeatMap Code for Grafana

I know there have been many requests for heatmaps that have hours on the first column and days on the subsuqient columns, and I had a working solution here: HeatMap by Day’s and hours (not date) - Grafana / Dashboards - Grafana Labs Community Forums. However, that required a MySQL (MariaDB) datasource.

The above referenced code does not work with MSSQL language, and I have found a solution that works with both datasources to share.

Disclamer
MSSQL and MySQL have different formattings for working with Datetime manipulation, I will break this up into one for each.

MSSQL

WITH Hours AS (
SELECT 0 AS Hour
UNION ALL
SELECT Hour + 1
FROM Hours
WHERE Hour < 23
)

SELECT
h.Hour,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 1 THEN 1 ELSE 0 END) AS Sunday,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 2 THEN 1 ELSE 0 END) AS Monday,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 3 THEN 1 ELSE 0 END) AS Tuesday,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 4 THEN 1 ELSE 0 END) AS Wednesday,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 5 THEN 1 ELSE 0 END) AS Thursday,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 6 THEN 1 ELSE 0 END) AS Friday,
SUM(CASE WHEN DATEPART(WEEKDAY, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) = 7 THEN 1 ELSE 0 END) AS Saturday
FROM Hours h
LEFT JOIN (
SELECT
DATEPART(Hour, SWITCHOFFSET(NameOfDateField, DATEPART(TZOFFSET, SYSDATETIMEOFFSET() AT TIME ZONE ‘Eastern Standard Time’))) AS Hour,
NameOfDateField
FROM dbo.table
) t ON h.Hour = t.Hour
GROUP BY h.Hour
ORDER BY h.Hour ASC;

My SQL (MariaDB)

WITH RECURSIVE Hours AS (
SELECT 0 AS Hour
UNION ALL
SELECT Hour + 1
FROM Hours
WHERE Hour < 23
)

SELECT
h.Hour,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 1 THEN 1 ELSE 0 END), 0) AS Sunday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 2 THEN 1 ELSE 0 END), 0) AS Monday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 3 THEN 1 ELSE 0 END), 0) AS Tuesday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 4 THEN 1 ELSE 0 END), 0) AS Wednesday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 5 THEN 1 ELSE 0 END), 0) AS Thursday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 6 THEN 1 ELSE 0 END), 0) AS Friday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) = 7 THEN 1 ELSE 0 END), 0) AS Saturday
FROM Hours h
LEFT JOIN (
SELECT
HOUR(CONVERT_TZ(NameOfDateField, ‘UTC’, ‘US/Eastern’)) AS Hour,
NameOfDateField
FROM YourTable

) t ON h.Hour = t.Hour
GROUP BY h.Hour
ORDER BY h.Hour ASC;

The built in table at the top of each is what allows the heatmap to show all 24 hours reguardless of data being present for said hour.

Now, all that is left is for you to format the cells and what you end up with is a beautiful heatmap!

I like to set the global column Width to 114, set the global cell type to Colored Background, and set the color to “Green-Yellow-Red”. Then I set a override for the Hours column, Add a color override for a single color, then add value mapping, add 24 entires, and set 0 to 12:00 AM etc.


image

Bonus
If you need to make multiple of these and do not want to manually set the overrides on all of them, after you set up one, you can view the JSON for this table, copy the overrides section, and paste it into your other tables, click save and your done!

Enjoy!

this section here is worrisome from performance and accuracy pov

what are you attemtping here

So, the table natively has all time in UTC, Grafana auto translates this to my local timezone if I call the date fields alone. When a modifier is applied to a date field (hour(), Month(), Year(), day()) then the result is in UTC. I have to manually convert the time to EST other wise I would have to update the querries twice a year when DST happens by using the Convert_TZ(field, +0,+4) to +5.