Search code examples
databasesortingselectgrafanainfluxdb

Show second to last item in Influx query (or ignore last)

I am using Grafana to show the number of entries added to the database every minute, and I would like to display the last recent fully counted value.

If I give the following command:

SELECT count("value") FROM "numSv" GROUP BY time(1m)

1615904700000000000 60
1615904760000000000 60
1615904820000000000 60
1615904880000000000 60
1615904940000000000 36

Grafana is going to display the last entry, which is still in the process of counting. How can I display the n[-1] entry, which has been fully counted?

Otherwise, how do I ask Influx to give me the same results excluding the last dataset?

P.S.: Using WHERE time > now() - 60s, etc... doesn't work.

Solution

  • Use "magic" Grafana time range math and select dashboard time range from now-1m/m to now-1m/m. That generates an absolute time range, which refers to last fully counted minute. Query is then standard with $timeFilter Grafana macro:

    SELECT count("value") FROM "numSv" WHERE $timeFilter