Time-weighted average: LOCF from last point until the end of the range

[milgner]

While trying to implement time-weighted averages into my application, I stumbled over this somewhat confusing behaviour:

I want to calculate the time-weighted average (just one value) for a measurement in a given time range.
The data should be interpreted in LOCF-fashion. Since it is spaced irregularly, I'm first running a query in the form

WITH prepared_data AS ((SELECT as value, '2021-07-21T22:00:00Z'::timestamp as ts
               FROM data
               WHERE data.ts <= '2021-07-21T22:00:00Z'
               ORDER BY data.ts DESC
               LIMIT 1)
              UNION ALL
              (SELECT value, ts
               FROM data
               WHERE data.ts > '2021-07-21T22:00:00Z'
                 AND data.ts <= '2021-07-21T23:00:00Z'
               ORDER BY ts))
SELECT * FROM prepared_data

Now the prepared_data expression contains both a starting value with the timestamp and the regular remaining records.
It seemed like just invoking average(time_weight('locf', ts, value)) would be enough.

But unfortunately, the last data point doesn't get carried forward until the end of the queried timerange. Which is understandable because there's no way for the function to know how long the queried timerange is supposed to be, which would be required for the locf mode.

This can be easily demonstrated using

SELECT average(time_weight('locf', ts, val))
  FROM (VALUES ('2023-02-06 11:00:00Z'::timestamp, 2)) as data(ts, val)

which only yields null as a result. Even adding a WHERE clause for some magic query introspection which apparently the functions do in some places, doesn't help:

SELECT average(time_weight('locf', ts, val))
  FROM (VALUES ('2023-02-06 11:00:00Z'::timestamp, 2)) as data(ts, val)
WHERE ts between '2023-02-06 11:00:00Z' and  '2023-02-06 12:00:00Z'

still produces null.

Now of course I can work around that by adding another UNION ALL statement to produce a "virtual" last row. But it seems to me like there should be a better approach. Not sure if I'm doing something wrong or whether these constructs should be enhanced in some way.
One thing that comes to mind is that instead of passing the interpolation method as a string, would be nice to have a separate interpolation type with a locf_interpolation constructor function which (optionally) receives both an expression to look up the preceding value as well as the time range. A corresponding linear_interpolation wouldn't need those then.

Looking forward to your suggestions.

[CoderPuppy]

I believe switching to interpolated_average fixes this problem by providing the interval (as start + duration) and the adjacent TimeWeightSummary's