I am a beginner in Grafana, and I need your help if possible. The version of Grafana I use is v8.5.2 and my data source is Postgresql. I am trying to create a bar chart in which the hours of the day will be depicted on the x axis (01:00, 02:00, …, 23:00, 00:00). However, every time I change the period, I get the dates and the hours instead of just taking 24 values. Additionally, on the y axis, I want to find the average for each specific hour for the chosen period. Below, you will find the queries and expressions I made.
query_1:SELECT date_trunc(‘hour’, d.time) AS “time”, avg(d.trend) as “value_trend”
FROM
(SELECT
table1 AS “time”,
table1.value_up - table1.value_down AS “trend”
FROM table1, (
SELECT
table1.time_stamp,
max(version) AS “version”
FROM table1
WHERE
val_no = 1 and
$__timeFilter(table1.time_stamp)
GROUP BY time_stamp
ORDER BY 1
) AS max_version
WHERE
table1.time_stamp = max_version.time_stamp and
table1.version = max_version.version and
val_no = 1 and
$__timeFilter(table1.time_stamp)
) d
GROUP by date_trunc(‘hour’, d.time)
HAVING ABS(avg(d.trend)) >= ${val_threshold}
ORDER BY 1
query_2: SELECT
date_trunc(‘hour’, time_stamp) AS “time”,
avg(total_value3) AS “total_value3”
FROM table2
WHERE
$__timeFilter(time_stamp)
GROUP BY date_trunc(‘hour’, time_stamp)
ORDER BY 1
and the following expressions: 1) A: $query_2>0, 2) B: $query_1>0 3) C: $A == $B
Hi again, I found the average for each hour of the day, but the result I get is not what I wanted. On the x axis, instead of having 24 values (the hours of the day), the below outcome is returned:
Thank you very much for your response, @yosiasz!
Although I tried the date_part function, the average I get is not correct. This is my query, which returns the right y values but not the x ones.
SELECT
EXTRACT(‘HOUR’ FROM date_trunc(‘hour’, time_stamp) AT TIME ZONE ‘Europe/Rome’) AS “time”,
AVG(value_1) AS “total_value_1”
FROM table_1
WHERE $__timeFilter(time_stamp)
GROUP BY “time”
ORDER BY 1
On the y axis, I calculate the average of each hour, whereas on the x axis, I want my time to range from 00:00 to 23:00. Whenever I use the date_part function, I don’t get the right average. However, my main problem is that, as you see in the picture above, the first 24 values on the x axis are the default value (1970-01-01 01:00), and the rest are the actual hours of the day, which appear as many times as the number of days I choose from the dropdown menu. So, in this case, I want each and every hour of the day to be displayed only once on the x axis.
I think I understood why these values are returned on the x axis. The reason is that the EXTRACT function for certain hours yields the 00:00 hour instead of the actual, for example, it returns the following: 00:00, 01:00, 02:00, 03:00, 04:00, 05:00, 06:00, 00:00, 08:00, 09:00, 10:00, 00:00, 12:00, 13:00, 14:00, 15:00, 16:00, 17:00, 18:00, 19:00, 00:00, 21:00, 22:00, 23:00. So, for the 7th, 11th and 20th hours of the day I get 00:00. Does anybody know how I can fix this?
