[Feature Request] Time Windowing Functions for Irregular Data

[OptrixAU]

Current Behaviour
When you request data with a time range, only the data recorded within that range is shown - not the records immediately before or after the range. If your data is irregularly spaced or not synchronised, this can result in graphs with incomplete or misleading data without having to make additional queries.

Desired behavior
It would be great if there was a query function that also included either the previous & next values, or interpolated these values for you to meet the bounds of your query.

Use Case #1

Fetching the values between two times, including those actual times (even if no recordings happened to be at that exact moment)

Rather than making the following queries...

 #Previous Value
  SELECT value FROM mymeasurement WHERE time < starttime ORDER BY time DESC LIMIT 1

   #Next Value
   SELECT value FROM mymeasurement WHERE time > endtime ORDER BY time ASC LIMIT 1

   #Window Values
   SELECT value FROM mymeasurement WHERE time >= starttime AND time <= endtime ORDER BY time ASC

You could instead try something more like this...

SELECT value FROM mymeasurement INWINDOW starttime endtime EDGES

Where in the above example, INWINDOW is equivalent to WHERE time >= starttime AND time <= endtime and EDGES is the instruction to also include the previous and next values, even though they are outside the stated range.

You might also have LEDGE (only include the previous value), REDGE (only include the next value) and TRIM_LINEAR (grab the previous and next values and linearly interpolate them with the the lowest and highest values that lie within the range to make the edge records fit within the time window.)

In the case of TRIM_LINEAR, an additional parameter exists to control what occurs when one of the edge values are null. For example, EDGES EITHER will return the value of whichever property is not null. PREVIOUS will use the earliest value, while LATEST will use the last value. The default is 'EITHER'.

Use Case #2

Additionally, it is very useful if you're trying to produce a 'snapshot' of the state of a system at a single specific point in time. Since it's unlikely that a user has happened to pick the exact moment that a recording has occurred (particularly if the intervals are irregular or the recordings are not synchronised), you'd normally have to make a couple of queries to interpolate a result.

It would be significantly easier if you could do something more like this...

SELECT value FROM mymeasurement, mymeasurement2 ATTIME time TRIM_LINEAR EITHER WHERE value != null

Using ATTIME would be identical to a call using INWINDOW with the same start and end time. The results would be a set of interpolated values for all of the measurements at that time.

If you wanted to do a form of interpolation other than linear interpolation, you'd use EDGES rather than TRIM_LINEAR and do it yourself.

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