Skip to main content
Since 1.15.0 Calculate the integral over an interval, while interpolating the interval bounds. Similar to integral, 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_average multiplied 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 integral should cover (if there is a preceding point)
intervalINTERVAL-✔The length of the interval which the time-weighted integral 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
unitTEXTsecondThe unit of time to express the integral in. Can be microsecond, millisecond, second, minute, hour, or any alias for those units supported by Postgres

​
Returns

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

​
Samples

Create a table to track irregularly sampled storage usage in bytes, and get the total storage used in byte-hours between January 1 and January 6. Use the ‘last observation carried forward’ interpolation method: 
-- Create a table to track irregularly sampled storage usage
CREATE TABLE user_storage_usage(ts TIMESTAMP, storage_bytes BIGINT);
INSERT INTO user_storage_usage(ts, storage_bytes) VALUES
    ('01-01-2022 20:55', 27),
    ('01-02-2022 18:33', 100),
    ('01-03-2022 03:05', 300),
    ('01-04-2022 12:13', 1000),
    ('01-05-2022 07:26', 817);


-- Get the total byte-hours used between Jan. 1 and Jan. 6
SELECT
    interpolated_integral(
        time_weight('LOCF', ts, storage_bytes),
        '01-01-2022',
        '5 days',
        NULL,
        NULL,
        'hours'
    )
FROM
    user_storage_usage;