How to change time aggregation period by using dashboard variable

Grafana Time Series Panel
,
1
  • What Grafana version (v9.4.3)

by selecting a dropdown variable from the top of the dashboard, I would like to be able to change the value of the timeGroup function

I have created a chart as shown below.

image
image1823Ă—380 99.5 KB

running from the below query.

SELECT $__timeGroup(TimeDate,6h,0) as time, COUNT(Fault) as value
FROM AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND Fault in ($Fault) and AGV in ($AGV)
GROUP BY $__timeGroup(TimeDate,6h,0)
ORDER BY time ASC

the query current has a fixed value of 6h for the fault count to take place in block of. I would like this value to be selectable if possible.

I have tried replacing the 6h with various forms of “frequency” taken from my variable, but I always get an error.

image
image475Ă—960 39.6 KB

Can anyone please advise if this is possible? and if so, what I am doing wrong.

Thank you

2

Take a look at the Query Inspector for a valid working query without variable.

3

Thanks for that, please see below. Not really sure what I am looking at though.

SELECT FLOOR(DATEDIFF(second, '1970-01-01', TimeDate)/21600)*21600 as time, COUNT(Fault) as value
FROM AGVfault
WHERE TimeDate BETWEEN '2023-10-07T09:03:00Z' AND '2023-11-06T10:03:00Z' AND Program <> 0 AND Program in ('1         ','2         ','3         ','4         ','5         ','6         ','7         ','8         ','9         ','11        ','12        ','13        ','14        ','15        ','16        ','17        ','18        ','19        ','20        ','21        ','22        ','23        ','24        ','25        ','26        ','27        ','28        ','29        ','30        ','31        ','32        ','33        ','34        ','35        ','41        ','42        ','43        ','50        ','51        ','52        ','61        ','62        ','92        ','94        ') AND Fault in ('actuator2ls alarm','actuatorls alarm','autochargefault alarm','drive1alarm alarm','drive2alarm alarm','estop alarm','frontscanner alarm','lowbattery alarm','offtrack alarm','rearscanner alarm','wifi alarm') and AGV in ('4         ','5         ','6         ','7         ','9         ','10        ','11        ','12        ','17        ','18        ','19        ','20        ','21        ','24        ','25        ','34        ','41        ','42        ','45        ','46        ','50        ','58        ','59        ','60        ','101       ','102       ','103       ','104       ','105       ','106       ','107       ','108       ','109       ','110       ','111       ','112       ','113       ','114       ','115       ','116       ','117       ','118       ','119       ','120       ','121       ','122       ','123       ','124       ','125       ','126       ','127       ','128       ','129       ','130       ','131       ','132       ','133       ','134       ','135       ','136       ','137       ','138       ','139       ','140       ','141       ','142       ','143       ','144       ','145       ','146       ','147       ','148       ','149       ','150       ','151       ','152       ','153       ','154       ','155       ','156       ','157       ','158       ','159       ','160       ','161       ','162       ','163       ','164       ','165       ','166       ','167       ','168       ','169       ','170       ','171       ','172       ','173       ','174       ','175       ','176       ','177       ','178       ','179       ','180       ','181       ','182       ','183       ','184       ','185       ','186       ','187       ','188       ','189       ','190       ','191       ','192       ','193       ','194       ','195       ','196       ','197       ','198       ','199       ','200       ','201       ','202       ','203       ','204       ','205       ','206       ','207       ','208       ','209       ','210       ','211       ','212       ','213       ','214       ','215       ','216       ','217       ','218       ','219       ','220       ','221       ','222       ','223       ','224       ','225       ','226       ','227       ','228       ','229       ','230       ','231       ','232       ','233       ','234       ','235       ','236       ','237       ','238       ','239       ','240       ','241       ','242       ','243       ','245       ','246       ','247       ','248       ','249       ','250       ','254       ','255       ','256       ','258       ','259       ','260       ','261       ','262       ','263       ','274       ','275       ','276       ','277       ','278       ','280       ','282       ','283       ','285       ','293       ','295       ')
GROUP BY FLOOR(DATEDIFF(second, '1970-01-01', TimeDate)/21600)*21600
ORDER BY time ASC

Can you advise?

4

I would recommend to use interval type variable, so you will have also “auto” value, e.g.:

obrázok
obrázok853×1512 72.4 KB

Then you can use it as simple $agg variable in available macros, e.g.:

$__timeGroup(sys_created_on,$agg,0),
1 Like
5

Perfect, Thankyou.

I also noticed I had used some brackets in error.

SELECT $__timeGroup(TimeDate,$Frequency,0) as time, COUNT(Fault) as value
FROM AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND Fault in ($Fault) and AGV in ($AGV)
GROUP BY $__timeGroup(TimeDate,$Frequency,0)
ORDER BY time ASC
1 Like