Subquery and time comparison

Hello,

Want to know what telegraf hosts are not reporting. Using InfluxDB 1.2.2. I thought the following query would work but seems the time comparison is not being done on the results of the subquery …

select host from (select last(uptime) FROM "system" group by host) where time < now() - 1d

Is that expected behavior? Any alternative method to achieve this?

Thanks

-Bharat

@BharatSharma Have you tried using the time comparison in the inside query?

Hello @jackzampolin - Thanks for the reply. I have. The query below results are the same as above (but makes sense to me in this context). For the original example above, I thought the time comparison would happen on the results of the query.

select host from (select last(uptime) FROM "system" where time < now() - 1d group by host)

Can anyone confirm expected behavior? Should we be able to do a time comparison on the results of a subquery?

I believe the current implementation of subqueries propagates where time > now() ... conditions to the inner query as an optimization.

Could you open a feature request detailing this use case.

Hi,
i have the same trouble concerning “where-condition-propagation”. I used the same query structure as mentioned above! In recent feature discussions this behaviour is described by the influx developers.

AFAIK today it is not possible to return points in time which are NOT within a specific time range!!!
An clause like “WHERE NOT IN” would be very nice…

My case is to determine the age of the latest point in time relative to now(), and then check if the user has not been active within the last n minutes.
The “elapsed()” function does not return the timespan with respect to the left/right interval boundary -> THIS woulld be a nice FEATURE!!! -> then the elapsed time concerning the first point would be calculated with respect to now()…

Why should a TSDB not have such an (important) time related functionality?

1 Like

I agree. Could you open feature request detailing this use case.

@larsL (and @michael) - Just FYI I did open a feature request here.

2 Likes