Hello everyone,
I am using Grafana 6.2.5. with PostgreSQL 11 including the timescale plugin. I recently played around with the time interval feature that results in a generated SQL beginning with the following 4 lines:
SELECT
time_bucket(‘1s’,sourcetimestamp) AS “time”,
‘metric001’ as metric,
avg(value)
Actually that works really well. The queries are much faster especially when I have selected a huge time range. The problem is that I have a lot of metrics with a high sampling rate (100 Hertz → 10ms). If I select a small time range like e.g. 10 seconds I would expect 1000 samples in the query result. Instead I only get 10 because the generated SQL still contains the ‘1s’ statement. In fact the min time interval parameter in the data sources configuration tab is set to ‘1s’. The problem is that if I try to set it to ‘10ms’, Grafana indicates a red rectangle around that parameter. There is no error message when I click “save & test” but it seems like in this case it is using the default value ‘1m’ that translates to ‘60s’ in the generated SQL. In the official Grafana doc (PostgreSQL data source | Grafana documentation) it is mentioned that ‘ms’ is valid as a time identifier. However that seems to be not the case.
Has anybody an idea what I am doing wrong?
At least I found a workaround for the problem that works for me. The key to the workaround is the global built-in variable $__interval_ms. You can find more information here.
You cannot do any SQL comparison operation with the $__interval because it is not an SQL datatype. However the $__interval_ms variable is an integer that allows to do a comparison operation after a “SELECT (CASE WHEN” statement. So the code in the query editor begins as follows:
SELECT (CASE WHEN $__interval_ms <= 1000 THEN (SELECT sourcetimestamp AS “time”) ELSE (SELECT $__timeGroupAlias(sourcetimestamp,$__interval))END),
‘metric001’ as metric,
avg(value)
That results in a generated SQL as follows:
SELECT (CASE WHEN 1000 <= 1000 THEN (SELECT sourcetimestamp AS “time”) ELSE (SELECT time_bucket(‘1s’,sourcetimestamp) AS “time”)END),
‘metric001’ as metric,
avg(value)
The $__interval_ms variable will never be smaller then 1000 because the min time interval parameter in the data sources configuration tab is set to ‘1s’, which is the smallest possible value.
Another way for a workaround would be to use the global built-in variables $__from and $__to which are both integers representing a UNIX timestamp with millisecond precision.