Subract Additional Time from $__timeFilter-CodePudding

I want to subract additional time in $__timeFilter in grafana. Like if I have selected Last 7 days, I want to run 2 queries which do a comparison like one query gives me avg cpu utilization for last 7 days and another one gives me avg cpu utilzation for now() - 14d to now() - 7d. And this is dynamic. I can get for 6hrs, 2days or anything selected.

My database is TimescaleDB and grafana version in 8.3.5

Edit Query is

select avg(cpu) from cpu_utilization where $__timeFilter(timestamp)

Whatever is selected in the time filter in grafana, the query is manipulated accordingly

Now with grafana understands this query becomes as following. if I select last 24hrs

select avg(cpu) from cpu_utilization where timestamp BETWEEN '2022-09-07 05:32:10' and '2022-09-08 05:32:10'

This is normal behaviour. Now I wanted that if I select last 24hrs, this query to behave as it is but an additional query becomes

select avg(cpu) from cpu_utilization where timestamp BETWEEN '2022-09-06 05:32:10' and '2022-09-07 05:32:10'

(I just don't want it for last 24hrs, but any relative time period selected in the filter)

CodePudding user response：

Interesting question! If I understood correctly, you could use the timestamp column as the reference as the grafana is already filtering by this to the comparison query. So you can get the min(timestamp) and max(timestamp) to know the limits of your period and then build something from it.

Like min(timestamp) - INTERVAL '7 days' would give you the start of the previous range, and max(timestamp) - INTERVAL '7 days' would offer the final scope.

CodePudding user response：

You can use the global variables $__to and $__from.

For example, ${__from:date:seconds} will give you a timestamp in seconds. You can then subtract 7 days (= 604800 seconds) from it and use it in your query's WHERE clause. Depending on your SQL dialect, that might be by using TIMESTAMP(), TO_TIMESTAMP() or something similar. So it would look similar to this:

[...] WHERE timestamp BETWEEN TO_TIMESTAMP(${__from:date:seconds}-604800) AND TO_TIMESTAMP(${__to:date:seconds}-604800) [...]