I have a PostgreSQL table where I want to take the difference between 2 COUNT() aggregates, but have the lower bound of the WHERE clause set to the minimum value of the time column (i.e., always start counting from the first entry as the lower bound), and the upper bound specified by Grafana for that point in time. The idea is to have a rolling difference, from the beginning of time, up to that point in time. Is there a way to achieve this? Thanks!
Think there are separate from and to variables , so you could do this with sub queries
It seems like version 6 will have those when it comes out. What might that look like? For the COUNT itself I’ll just use filters to get them, for example:
– something like $__timeFilter goes here, but lower bound is set to min(time_col)
COUNT(id) filter (WHERE foo = ‘bar’) - COUNT(id) filter (WHERE foo = ‘baz’) as y_value
Here’s a workaround that may work for you depending on if you don’t care that transactions AFTER the graph time window are also gathered (but not used/displayed).
- Select the edit button for your query
- change ‘$timeFilter’ to ‘time <= now()’
Once you add this change, you can’t really go back to the drop-down-query-maker otherwise the time will revert back to $timeFilter.
FYI: I’m on Grafana-6.0.0-beta3 because I came here looking for the same answer and haven’t yet figured out the ‘__from' and '__to’ variables.
You know what, I just read you’re trying this for PostgresQL. My change above is for InfluxDB so you’ll need to change ‘time <= now’ to what query would just pull in all transactions.