Grafana built in timezone vs extract

Grafana Dashboards
1

9.5.1

I have been informed my pretend heatmap is not displaying the data properly.

My company uses EST time, while I use CST. All of the visulazations are handling this properly as its using browser time to display the data to us. However, in my heat map table I believe I just discovered its using UTC time with the extract command, then I am hardcoding the hours, so Grafana cannot do a conversion.

How can I achieve this without having to come in here every DST change. I was just going to add 5 to each of the “AS xx:xx” lines, but that would require me to change it twice a year…

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘0’ THEN 1 ELSE 0 END) AS ‘00:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘1’ THEN 1 ELSE 0 END) AS ‘01:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘2’ THEN 1 ELSE 0 END) AS ‘02:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘3’ THEN 1 ELSE 0 END) AS ‘03:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘4’ THEN 1 ELSE 0 END) AS ‘04:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘5’ THEN 1 ELSE 0 END) AS ‘05:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘6’ THEN 1 ELSE 0 END) AS ‘06:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘7’ THEN 1 ELSE 0 END) AS ‘07:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘8’ THEN 1 ELSE 0 END) AS ‘08:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘9’ THEN 1 ELSE 0 END) AS ‘09:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘10’ THEN 1 ELSE 0 END) AS ‘10:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘11’ THEN 1 ELSE 0 END) AS ‘11:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘12’ THEN 1 ELSE 0 END) AS ‘12:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘13’ THEN 1 ELSE 0 END) AS ‘13:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘14’ THEN 1 ELSE 0 END) AS ‘14:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘15’ THEN 1 ELSE 0 END) AS ‘15:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘16’ THEN 1 ELSE 0 END) AS ‘16:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘17’ THEN 1 ELSE 0 END) AS ‘17:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘18’ THEN 1 ELSE 0 END) AS ‘18:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘19’ THEN 1 ELSE 0 END) AS ‘19:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘20’ THEN 1 ELSE 0 END) AS ‘20:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘21’ THEN 1 ELSE 0 END) AS ‘21:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘22’ THEN 1 ELSE 0 END) AS ‘22:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘23’ THEN 1 ELSE 0 END) AS ‘23:00’

2

I attempted to convert_tz, and it just sets all of my data to 0

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 0 THEN 1 ELSE 0 END) AS ‘00:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 1 THEN 1 ELSE 0 END) AS ‘01:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 2 THEN 1 ELSE 0 END) AS ‘02:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 3 THEN 1 ELSE 0 END) AS ‘03:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 4 THEN 1 ELSE 0 END) AS ‘04:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 5 THEN 1 ELSE 0 END) AS ‘05:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 6 THEN 1 ELSE 0 END) AS ‘06:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 7 THEN 1 ELSE 0 END) AS ‘07:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 8 THEN 1 ELSE 0 END) AS ‘08:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 9 THEN 1 ELSE 0 END) AS ‘09:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 10 THEN 1 ELSE 0 END) AS ‘10:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 11 THEN 1 ELSE 0 END) AS ‘11:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 12 THEN 1 ELSE 0 END) AS ‘12:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 13 THEN 1 ELSE 0 END) AS ‘13:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 14 THEN 1 ELSE 0 END) AS ‘14:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 15 THEN 1 ELSE 0 END) AS ‘15:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 16 THEN 1 ELSE 0 END) AS ‘16:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 17 THEN 1 ELSE 0 END) AS ‘17:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 18 THEN 1 ELSE 0 END) AS ‘18:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 19 THEN 1 ELSE 0 END) AS ‘19:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 20 THEN 1 ELSE 0 END) AS ‘20:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 21 THEN 1 ELSE 0 END) AS ‘21:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 22 THEN 1 ELSE 0 END) AS ‘22:00’

, SUM(CASE WHEN EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘UTC’, ‘America/New_York’))) = 23 THEN 1 ELSE 0 END) AS ‘23:00’

3

When the mariadb has not had the timezone tabled expanded/imported into the db itsself. The above will not work.

However, you can get around this, if you know your source data’s timezone, you can use the + and - from utc to reformat the data, I assume, this will require you to edit the visulazations twice a year for DST.

for example:
EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)))