Support a sample function: Nth point in interval

[firasdib]

Bug report

System info:
Influx v1.3
Ubuntu

Steps to reproduce:
We are trying to sample our data. We have millions of points and want to fetch every Nth point in a given interval. We have implemented a solution that calculates the time difference in this interval and then groups by it to receive the correct amount of points.

SELECT last(value) as value FROM measurement WHERE time >= '...' AND time <= '...' GROUP BY time(calculated_time) fill(none)

The amount of points returned seems to be correct, but the dates are not.

Expected behavior:
I expect the data to be returned to have the correct timestamp as in the database, regardless of the time used in the aggregation function.

Actual behavior:
The time returned seems to be a multiple of the aggregated time. That is, if my aggregation is GROUP BY time(7m) then the points seem to a multiple of 7 apart.

See this:

> SELECT value FROM "measurement" WHERE time >= '2016-01-01T00:00:00Z' AND time <= '2017-01-01T00:00:00Z' LIMIT 5;
name: measurement
time                 value
----                 -----
2016-01-01T00:00:00Z 61.111
2016-01-01T01:00:00Z 183.673
2016-01-01T02:00:00Z 200
2016-01-01T03:00:00Z 66.667
2016-01-01T04:00:00Z 97.959

In comparison to:

> SELECT last(value) as value FROM "measurement" WHERE time >= '2016-01-01T00:00:00Z' AND time <= '2017-01-01T00:00:00Z' GROUP BY time(23m) fill(none) LIMIT 5;
name: measurement
time                 value
----                 -----
2015-12-31T23:44:00Z 61.111
2016-01-01T00:53:00Z 183.673
2016-01-01T01:39:00Z 200
2016-01-01T02:48:00Z 66.667
2016-01-01T03:57:00Z 97.959

[phemmer]

This isn't a bug, but is the documented behavior: https://docs.influxdata.com/influxdb/v1.3/query_language/data_exploration/#common-issues-with-basic-syntax

That said, there have been several requests for features which would allow you to control the time value returned in the results, such as #5793 & #5926

[firasdib]

@phemmer Is there any workaround?

[timhallinflux]

Have you reviewed the sample function that is available via Kapacitor? Does this sound more like what you are attempting to do: https://docs.influxdata.com/kapacitor/v1.3/nodes/sample_node

I realize that it is another "part" to add to the setup. However, for more advanced data handling use cases we typically see Kapacitor used as co-processor with the database to achieve these kinds of results.

[firasdib]

@timhallinflux That looks interesting. One important criteria for us is that the points of both the start and the end of the interval are included. Is this still possible with the sampling in kapacitor?

[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.