CODEWITHSUNDEEP
postgresqlgrafanatimescaledb
Kush Rohra
Kush Rohra

Reputation: 13

Subract Additional Time from $__timeFilter

I want to subtract additional time in $__timeFilter in Grafana. Like if I have selected Last 7 days, I want to run 2 queries that do a comparison one query gives me avg cpu utilization for the last 7 days and another one gives me avg cpu utilization for now() - 14d to now() - 7d. And this is dynamic. I can get it for 6hrs, 2 days, 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 understanding this query becomes as follows. if I select the 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 behavior. Now I wanted that if I select the 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 the last 24hrs, but any relative time period selected in the filter)

Answer: https://stackoverflow.com/a/73658919/14817486

Upvotes: 0

Views: 3812

Answers (2)

dnnshssm
dnnshssm

Reputation: 1305

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) [...]

Upvotes: 2

jonatasdp
jonatasdp

Reputation: 1412

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.

Upvotes: 1

Related Questions