queries should be able to access closest point before query time range

[beckettsean]

There are a number of cases, most importantly DERIVATIVE() and FILL(previous), where the previous vaule of the field is important. However, there is no way to find that value now.

If there are no matching points in the time range, FILL(previous) will simply fail, which isn't the expected behavior. It should be able to grab the last recorded value and use that, even if it's from a point before the query time range.

[BrannonKing]

+1. Go back until you have data!

[jsternberg]

This works correctly for aggregate queries, but does not work for raw queries. So if you have data every 10 seconds, you can do this and it will work correctly:

SELECT derivative(first(value)) FROM cpu WHERE time > now() - 10m GROUP BY time(1m)

But this won't work with the same data:

SELECT derivative(value) FROM cpu WHERE time > now() - 10m

[beckettsean]

@jsternberg what would be the behavior with fill(previous)?

Why does the latter query fail? Provided there are at least two points in the range, shouldn't that return something?

[jsternberg]

I've got an interesting edge case for this. So say that we are doing the following query:

SELECT mean(value) FROM cpu
    WHERE time >= '2010-01-01T12:05:00Z'
        AND time < '2010-01-01T13:05:00Z'
    GROUP BY time(10m) FILL(previous)

The start time of the query is in the middle of an interval. If there are no points in the 12:00 - 12:10 range, how should the previous interval be sampled? Should it look at 11:55 - 12:05 or 11:50 - 12:00? It seems like it would use the latter to be consistent, but then we have a weird case where 11:50 - 12:00 get looked at for an interval and 12:00 - 12:05 just gets ignored.

[beckettsean]

@jsternberg it's not terribly intuitive, but to be consistent with the behavior of GROUP BY time(x, y) I think we want the 11:50-12:00 bucket for the previous, assuming there's nothing between 12:00 and 12:10. The other buckets in the query will be on xx:x0 boundaries, so the previous bucket should be, too.

If there's data between 12:00 and 12:05, then I think we should use those value(s). The user asked for 10 minute buckets but started them off-center, if you will. That means that they want 10m groupings, but starting at 12:05, so the first and last grouping will have half a bucket's worth of data. If there's data in the other half of the bucket we should use it. It was excluded from the time range, but the fill(previous) explicitly says go back until there's data if there's none in the provided range.

If the user wanted to actually use the 11:55-12:05 bucket they would use GROUP BY time(10m,5m) to start the 10m buckets on the xx:x5 boundary instead of xx:x0. If there were then nothing in the 12:05-12:15 bucket, it would look to the previous bucket for the value.

[stettix]

+1

[skladd]

I'm also hitting this issue trying to calculate the distance traveled from odometer values:
SELECT DIFFERENCE(LAST("odometer")) FROM "gps" WHERE time [...] GROUP BY time(1d)
The first distance is missing if there is a data gap at the beginning of the time range.
My workaround now is to to query the "real" last value separately, query the odometer values and perform the calculation outside of InfluxDB.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[matejsp]

This issue is very important and needs to be resolved. Doing multiple queries to get latest value outside the range has also bigger performance impact.

Why is bot closing as wontfix?

[matejsp]

Is this duplicate of #6878 ?

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[hildeb]

Reopen, since still annoying everybody

[lelmarir]

here a solution using flux:

import "experimental"

bucket = "test/autogen"
measurement = "mymeasurement"
field = "v"
start = 2020-09-17T13:50:00Z
stop = 2020-09-17T14:10:00Z
aggregateWindowEvery = 1ms

previous = from(bucket: bucket)
  |> range(start:  experimental.subDuration(d: 24h, from: start), stop: start)
  |> filter(fn: (r) => r._measurement == measurement and (r._field == field))
  |> last()
  |> aggregateWindow(every: 1s, fn:mean, createEmpty: false)
  
running = from(bucket: bucket)
  |> range(start: start, stop: stop)
  |> filter(fn: (r) => r._measurement == measurement and (r._field == field))
  |> fill(usePrevious: true)
  |> aggregateWindow(every: aggregateWindowEvery, fn: mean, createEmpty: false)
  
union(tables: [previous, running])
  |> window(every: inf)

[tillsc]

My approach was slightly different: https://gist.github.com/tillsc/2d71f3e89409049833e75ed22689ad40

[Cjkeenan]

here a solution using flux:

import "experimental"

bucket = "test/autogen"
measurement = "mymeasurement"
field = "v"
start = 2020-09-17T13:50:00Z
stop = 2020-09-17T14:10:00Z
aggregateWindowEvery = 1ms

previous = from(bucket: bucket)
  |> range(start:  experimental.subDuration(d: 24h, from: start), stop: start)
  |> filter(fn: (r) => r._measurement == measurement and (r._field == field))
  |> last()
  |> aggregateWindow(every: 1s, fn:mean, createEmpty: false)
  
running = from(bucket: bucket)
  |> range(start: start, stop: stop)
  |> filter(fn: (r) => r._measurement == measurement and (r._field == field))
  |> fill(usePrevious: true)
  |> aggregateWindow(every: aggregateWindowEvery, fn: mean, createEmpty: false)
  
union(tables: [previous, running])
  |> window(every: inf)

Forgive my ignorance, but is this type of fix implementable using Grafana? It is crazy that an issue like this has not been fixed in the 5+ years it has been reported.