i want to show a text, based on system time. how can i configure this. I can create a query on SQL but cannot shown this Grafana panel. my example SQL query is:
" IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘12:00’ and ‘12:30’
PRINT ‘YEMEK ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘04:00’ and ‘04:30’
PRINT ‘YEMEK ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘20:00’ and ‘20:30’
PRINT ‘YEMEK ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘02:00’ and ‘02:10’
PRINT ‘MOLA ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘06:00’ and ‘06:10’
PRINT ‘MOLA ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘10:00’ and ‘10:10’
PRINT ‘MOLA ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘14:00’ and ‘14:10’
PRINT ‘MOLA ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘18:00’ and ‘18:10’
PRINT ‘MOLA ZAMANI’
ELSE IF CONVERT(VARCHAR(5),GETDATE(),108) BETWEEN ‘22:00’ and ‘22:10’
PRINT ‘MOLA ZAMANI’
ELSE
PRINT ‘CALISMA ZAMANI’ "
First of thank you for your reply. Actually, what I want to do is to change the text according to certain time intervals. With which method and with which panel can I do this. For example: If the system time is between 16:00 and 16:30, the panel will say “WORK” and if it is between 16:31 and 18:00, the text will change to “SLEEP”. SQL is not a must, it can be with any language.
Using power of InfluxDB’s Flux and Grafana’s Stat Panel:
import "array"
import "date"
array.from(rows :[{h : date.hour(t : now()), m : date.minute(t : now())}])
|> map(fn: (r) => ({ r with msg:
if (r.h == 12 or r.h == 4 or r.h == 20) and r.m >= 0 and r.m <= 30 then "YEMEK ZAMANI"
else if (r.h == 2 or r.h == 6 or r.h == 10 or r.h == 14 or r.h == 18 or r.h == 22) and r.m >= 0 and r.m <= 10 then "MOLA ZAMANI"
else "CALISMA ZAMANI"}))
Thank you verymuch @ebabeshko . This is the first time I’ve heard of InfluxDB. How should I go about adding the database to Grafana? As far as I can see, the work you exemplify is exactly what I want.
if CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '12:00' and '12:30'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '04:00' and '04:30'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '20:00' and '20:30'
begin
select 'YEMEK ZAMANI' as onduty
end
else if CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '02:00' and '02:10'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '06:00' and '06:10'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '10:00' and '10:10'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '14:00' and '14:10'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '18:00' and '18:10'
or CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '22:00' and '22:10'
begin
select 'MOLA ZAMANI' as onduty
end
ELSE
begin
select 'CALISMA ZAMANI' as onduty
end
i have serious doubts about CONVERT(VARCHAR(5),GETUTCDATE(),108) BETWEEN '02:00' and '02:10'
this conversion creates a varchar and how can you compare a range using BETWEEN 2 varchar values. Might best to use a numeric value range, convert that hour/minute range to a numeric value. not a grafana issue though
Thank you for your reply. I’m really new to Grafana and I’m sorry for taking your time. I will test what is written here in order. And now I am trying the canvas panel. But where exactly should I write the query specified?
Thanks again. I finally made progress. but as you said, there is a problem with getting the time difference. the first code I wrote works fine, but it did not work properly with the select query. i will find a way. apart from that, can we intervene in the panel colors according to the time as we change the text in the panel?
Hello again @ebabeshko . I tested your code and waited for the time 10:00 and nothing happened the text is still “CALISMA ZAMANI” . I checked your code. normally when the hour is 10 and minute is 0 the text will be “MOLA ZAMANI”. what do you think could be the reason?
In my example above UTC time is used. If you need local timezone, it is necessary to add the following lines before array.from and specify the time zone you need:
Yes this solution couse an error but i fix it this way. Last thing i must refresh page for change text. Can i set an interval for auto refresh? @ebabeshko
This could happen if you are using InfluxDB for Windows, but there is a workaround for that described here.
By default, Grafana does not automatically refresh the dashboard. Queries run on their own schedule according to the panel settings. However, if you want to regularly refresh the dashboard, click the down arrow next to the Refresh dashboard icon, and then select a refresh interval.
Got it, thank you. Yes, as a solution, I can change the refresh time from there. My use case is that these panels will be shown on TV with a playlist and the user has no intervention option. Can the query be organized in such a way that it constantly refreshes itself or I will have to test it this way for a while.
You choose refresh rate that you want in the top right corner, save the dashboard, and all panels on that dashboard will be refreshed with the chosen refresh rate.
Hello again @ebabeshko The code works very well. but I couldn’t find how I should edit the intervals that change the time part. for example between 12:40 and 13:10.
The code works very well. but I couldn’t find how I should edit the intervals that change the time part. for example between 12:40 and 13:10.