Implement interpolate() functionality

[shypard]

Hi!

TimescaleDB offers a function for linear interpolation: interpolate() Documentation.

As django-timescaledb does not support this feature yet, I tried integrating the the functionality, however I am facing an issue with providing the optional arguments prev and next, which are needed, if you want to interpolate the values for the first and last bucket in the desired range.

The idea was to add the following Interpolate class to the expressions.py:

class Interpolate(models.Func):
    function = 'interpolate'
    name = 'interpolate'

    def __init__(self, expression, prev, next, *args, **kwargs):
        super().__init__(expression, prev, next, *args, **kwargs) # naive approach :)

And use it like this:

# set range
start = timestamp.replace(hour=9, minute=0, second=0)
end = timestamp.replace(hour=16, minute=0, second=0)

# lookup queries for data _before_ and _after_ the gapfill range
prev = Metric.timescale.filter(time__lte=start).order_by('-time')[0]
next = Metric.timescale.filter(time__gte=end).order_by('time')[0]

metrics = (Metric.timescale
    .values('time', 'temperature')
    .annotate(hour=TimeBucketGapFill('time', '1 hour', start, end, datapoints=None))
    .annotate(avg=Avg('temperature'))
    .annotate(interpolate=Interpolate(Avg('temperature'), prev=prev, next=next))
    .values('hour', 'avg', 'interpolate')
    .order_by('hour')
    .distinct())

However, the prev and next queries are evaluated into objects (Metric object (1) and Metric object (5)), which is obviously not compatible:

SELECT DISTINCT 
    time_bucket_gapfill(INTERVAL 1 hour, "metrics_metric"."time", 2022-07-05 09:00:00.896143+00:00, 2022-07-05 16:00:00.896143+00:00) AS "hour",
    AVG("metrics_metric"."temperature") AS "avg",
    interpolate(AVG("metrics_metric"."temperature"), Metric object (1), Metric object (5)) AS "interpolate"
FROM "metrics_metric"
GROUP BY 
    "metrics_metric"."time",
    "metrics_metric"."temperature",
    time_bucket_gapfill(INTERVAL 1 hour, "metrics_metric"."time", 2022-07-05 09:00:00.896143+00:00, 2022-07-05 16:00:00.896143+00:00)
ORDER BY "hour" ASC

This results in an error:

File "/home/<name>/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: can't adapt type 'Metric'

In the interpolate() Documentation the prev and next are sub-select statements, using the Python ORM they are getting evaluated "beforehand".

Do you have any idea, what should adapted in order to get it to work?

thanks & br
Chris :)