I have a problem that limits me quite a bit. We are trying to sample our data by grouping time. We have millions of points and we want to get every N-th point for a given interval. We implemented a solution that calculates the time difference in this interval and then groups it to get the right number of points.
SELECT last(value) as value FROM measurement WHERE time >= '...' AND time <= '...' GROUP BY time(calculated_time) fill(none)
The number of returned points seems correct, but the dates do not match.
See the results below:
No sample
> 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
With selection
> 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
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. Instead, the return time appears to be a multiple of the total time. That is, if my aggregation GROUP BY time(7m), then the points seem to be short by 7.
, , , ? , . ( ).