I’d like to query InfluxDB using InfluxQL and exclude any rows from 0 to 5 minutes after the hour.
Seems pretty easy to do using the time field (the number of nanoseconds since the epoch) and a little modulus math. But the problem is that any WHERE clause with even the simplest calculation on time returns zero records.
How can I exclude any rows from 0 to 5 minutes after the hour?
# Returns 10 records
SELECT * FROM "telegraf"."autogen"."processes" WHERE time > 0 LIMIT 10
# Returns 0 records
SELECT * FROM "telegraf"."autogen"."processes" WHERE (time/1) > 0 LIMIT 10
Hello @Unique,
Welcome!
Out of curiosity are you new to InfluxDB? If so, why are you using InfluxDB v1 instead of v2?
With InfluxQL you’d have to explicitly state the timestamps that you want data for:
// 5 minutes after the hour
Where time > '2022-04-26T20:07:05Z'
@Unique If you wanted to filter out the first 5 minutes of data for multiple hours you’d have to either list all of the ranges with InfluxQL with an OR statement (which could be tedious/impractical)
OR you could use Flux, the data query and scripting language in InfluxDB v2 to do this programatically.
Probably with a filter() function.
If this is the type of problem you’re trying to tackle, let me know and I can supply a flux query.
“why are you using InfluxDB v1 instead of v2” → metrics are being stored on a Raspberry Pi 3 and InfluxDB documentation says InfluxDB v2 needs a Raspberry Pi 4. I haven’t explored putting v2 on a Pi 3 past what the documentation says.
“I can supply a flux query” → If you have it handy, then could you include it below so that when I revisit this issue in the future that it’s there. The calculation I would use might be something like (time / 1000000000) % 3600 / 60 > 5
Which is the preferred language moving forward InfluxQL or Flux?
