Need your help to understand how "$__timeFilter(column)" works

1

Hi,

Hello
I’m running graphana 5.3 and when i use the variable “$__timeFilter(column)”
I have an issue, with the time generated.

This is the generated sql code :
select * from ocum.volumehistorymonth
where volumeID = “2959” and periodEndTime BETWEEN ‘2018-11-05T09:31:13Z’ AND ‘2018-11-12T09:31:13Z’ <-- this indeed conform to what grafana help explained

The query is correct, i test the query generated by grafana directly on my database but the “T” and “Z” are generated warning (), my column periodEndTime is type : TIMESTAMP and so the date stored are in the format : “2018-11-06 02:00:00”

So the first question is : do we have a setting where we could avoid having this “Z and T”?

But as the generated query is returning correct value (even if i have warning) i’m not sure why inside grafana i get this error : “Found no column named time or time_sec”

Any help welcome :smile:

Thanks
Flo

2

this macro will be changed in V5.3.3 and the bugs will be fixed

3

Just tried version 5.3.3 and seems not fixed

4

5.3.4 just released with this fix.

5

updated…but bug is still there.

Query in grafana:
select * from ocum.volumehistorymonth
where volumeID = “2959” and $__timeFilter(periodEndTime)

Generated SQL

select * from ocum.volumehistorymonth
where volumeID = “2959” and periodEndTime BETWEEN FROM_UNIXTIME(1539522062) AND FROM_UNIXTIME(1542117662)

Error in grafana, still “no column named time or time_sec”

Workaround

select *
from (select dfKBytesTotal/1024/1204,volumeId, periodEndTime as time_sec from ocum.volumehistoryweek) as bibi

where volumeId = “2959” and $__timeFilter(time_sec)

Thanks

6

You cannot use select * from ... you must do a select periodEndTime as time from ... or use the macro function select $__time(periodEndTime) from ....

7

You are right the right query is that one

In Grafana:
select (dfKBytesTotal/1024/1204) from ocum.volumehistorymonth
where volumeID = “2959” and $__timeFilter(periodEndTime)

the sql generated is :slight_smile:
select dfKBytesTotal/1024/1204 from ocum.volumehistorymonth
where volumeID = “2959” and periodEndTime BETWEEN FROM_UNIXTIME(1539522417) AND FROM_UNIXTIME(1542118017)

And this is correct, when i copy past the query generated to my database editor(correctly generated at the screen but not send to the database); it is working i got the correct result for the period of time selected inside grafana.

Another proof (from my point of view) that the bug is still there is that the workaround is working.

select volused,time_sec
from (select dfKBytesTotal/1024/1204 as volused,volumeId, periodEndTime as time_sec from ocum.volumehistoryweek) as bibi

where volumeId = “2959” and $__timeFilter(time_sec)

With this workaround i give grafana what is waiting for : a column named time_sec and i got the graph
Flo