Skip to main content
Since 1.14.0 Calculate the time-weighted average over an interval, while interpolating the interval bounds. Similar to average, but allows an accurate calculation across interval bounds when data has been bucketed into separate time intervals, and there is no data point precisely at the interval bound. For example, this is useful in a window function. Values from the previous and next buckets are used to interpolate the values at the bounds, using the same interpolation method used within the TimeWeightSummary itself. Equal to interpolated_integral divided by the elapsed time.

​
Arguments

NameTypeDefaultRequiredDescription
twsTimeWeightSummary-✔The input TimeWeightSummary from a time_weight() call
startTIMESTAMPTZ-✔The start of the interval which the time-weighted average should cover (if there is a preceding point)
intervalINTERVAL-✔The length of the interval which the time-weighted average should cover
prevTimeWeightSummaryNULLThe TimeWeightSummary from the prior interval, used to interpolate the value at start. If NULL, the first timestamp in tws is used for the starting value. The prior interval can be determined from the Postgres lag() function
nextTimeWeightSummaryNULLThe TimeWeightSummary from the next interval, used to interpolate the value at start + interval. If NULL, the last timestamp in tws is used for the starting value. The next interval can be determined from the Postgres lead() function

​
Returns

ColumnTypeDescription
averageDOUBLE PRECISIONThe time-weighted average for the interval (start, start + interval), computed from the TimeWeightSummary plus end points interpolated from prev and next

​
Samples

Calculate the time-weighted daily average of the column val, interpolating over bucket bounds using the ‘last observation carried forward’ method: 
SELECT
    id,
    time,
    interpolated_average(
        tws,
        time,
        '1 day',
        LAG(tws) OVER (PARTITION BY id ORDER by time),
        LEAD(tws) OVER (PARTITION BY id ORDER by time)
    )
FROM (
    SELECT
        id,
        time_bucket('1 day', ts) AS time,
        time_weight('LOCF', ts, val) AS tws
    FROM foo
    GROUP BY id, time
) t