Influx Query Boundary Data (When Recording By Exception)

I’m from the industrial market, and InfluxDB is very close to being an ideal product for us. But there is one major issue that I can’t seem to get around, and by the look of the Issues list on Github, it’s not an uncommon one.

The problem comes from sparse data. Normally, efficient historians only record changes in data. So when a valve spends all week open, you have 1 data point recorded instead of 604,800.

The thing that is killing me is that using Influx, there’s no way to get data interpolated around the boundaries of your query (ie. the ‘from’ and ‘to’ times). So if I have the following data…

9:00: 20
10:00: 10
22:00: 10
23:00: 0

If I ask for 11:00 to 21:00, I get nothing. I’d like to get a value of 10 and 11:00 and another 10 at 21:00.

If I ask for 9:01 to 22:59, and use fill, I get nothing until 10:00, then a flat line all the way through to 22:59, and no hint of that drop off to 0 on the other side. What I’d like to see is a line falling from 19.9 to 10:00, straight line till 22:00, and a dropoff to 10.1 on towards 22:59. This gives very misleading results.

The irritating thing is that boundary queries have been built into industrial historians for decades - it’s part of the OPC standard, and that’s ancient. I’m a little shocked that there isn’t an obvious mechanism for this in a product that has such as similar basic idea.

Is there anything in InfluxQL or Flux that allows me to get interpolated boundary data, or ask for the records just before the first, and just after the last item within the query timeframe so I can handle the interpolation myself?

I believe we are tracking this issue here:

If you could take a look and comment on whether this will meet your needs, that would be appreciated. Thanks!

That’s somewhat close, but it isn’t very clear on one thing. The existing fill function for InfluxQL already has an interpolate function (but it’s MISSING in Flux?).

The catch is that when you mention ‘furthest previous’ and ‘farthest next’, this MUST include data points outside the filter time window.

Right now, Fill only considers points within the window, which causes issues around the boundary. The existing fill function would be fine for me if boundary data was provided.