Unexpected result with WHERE time clause

Telegraf
, ,
1

Hi everyone,

I store metrics in influxdb every 5 minutes, sum them up by 5 minutes group, and get the difference between each subsequent value:

> SELECT difference(sum("COMP")) FROM "counts" WHERE time >= now() - 30m GROUP BY time(5m)
name: gtm_counts
time                 difference
----                 ----------
2018-10-09T14:30:00Z 7
2018-10-09T14:35:00Z 12
2018-10-09T14:40:00Z 29
2018-10-09T14:45:00Z 28
2018-10-09T14:50:00Z 13

I am able to narrow the timeframe of the results using operators on time:

> SELECT * FROM (SELECT difference(sum("COMP")) FROM "counts" WHERE time >= now() - 30m GROUP BY time(5m)) WHERE time <= '2018-10-09T14:40:00Z'
name: gtm_counts
time                 difference
----                 ----------
2018-10-09T14:25:00Z 12
2018-10-09T14:30:00Z 7
2018-10-09T14:35:00Z 12

> SELECT * FROM (SELECT difference(sum("COMP")) FROM "gtm_counts" WHERE time >= now() - 30m GROUP BY time(5m)) WHERE time >= '2018-10-09T14:40:00Z'
name: gtm_counts
time                 difference
----                 ----------
2018-10-09T14:45:00Z 28
2018-10-09T14:50:00Z 13

The full list of operators is provided in the documentation, unfortunately some of them don’t give the expected results, either returning nothing when an actual value should be returned, or even returning an error:

> SELECT * FROM (SELECT difference(sum("COMP")) FROM "counts" WHERE time >= now() - 30m GROUP BY time(5m)) WHERE time >= '2018-10-09T14:45:00Z'
name: gtm_counts
time                 difference
----                 ----------
2018-10-09T14:50:00Z 13
2018-10-09T14:55:00Z 15
> SELECT * FROM (SELECT difference(sum("COMP")) FROM "counts" WHERE time >= now() - 30m GROUP BY time(5m)) WHERE time = '2018-10-09T14:45:00Z'
> SELECT * FROM (SELECT difference(sum("COMP")) FROM "counts" WHERE time >= now() - 30m GROUP BY time(5m)) WHERE time != '2018-10-09T14:45:00Z'
ERR: invalid time comparison operator: !=

Note that these operators works perfectly when I query my data without a subquery involved:

> SELECT "COMP" FROM "gtm_counts" WHERE time = '2018-10-09T15:30:05Z'
name: counts
time                 COMP
----                 ----
2018-10-09T15:30:05Z 2
2018-10-09T15:30:05Z 1
2018-10-09T15:30:05Z 1
...

What am I doing something wrong? I can easily filter on the difference value, but I need some enlightenment about the meaning and behavior of the time clause in the external query.

2

What are you trying to do ? Why do you want to filter the time in the main query ?

The subquery is executed first so you should have two time condition in the subquery.

3

Initially, I was trying to get the differences between my sums not every 5 minutes (the data sampling rate) but at another rate (for instance every 30m, or every hour) without resorting to a continous query (so as not to store some metrics twice…).

In other words, I was trying to skip n sums out of m. I ended up with this main and sub queries and these unexpected result when adding the where time clause in the main query.

I don’t get it… sure, the subquery is executed first, and I want to to filter the result of the subquery by picking some timestamps. Why should I have a second condition in the subquery?