interpolated_integral()

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.

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;

Arguments

The syntax is:

interpolated_integral(
    tws TimeWeightSummary,
    start TIMESTAMPTZ,
    interval INTERVAL
    [, prev TimeWeightSummary]
    [, next TimeWeightSummary]
    [, unit TEXT]
) RETURNS DOUBLE PRECISION

Name	Type	Default	Required	Description
tws	TimeWeightSummary	-	✔	The input TimeWeightSummary from a time_weight() call
start	TIMESTAMPTZ	-	✔	The start of the interval which the time-weighted integral should cover (if there is a preceding point)
interval	INTERVAL	-	✔	The length of the interval which the time-weighted integral should cover
prev	TimeWeightSummary	NULL		The 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 PostgreSQL lag() function
next	TimeWeightSummary	NULL		The 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 PostgreSQL lead() function
unit	TEXT	second		The unit of time to express the integral in. Can be microsecond, millisecond, second, minute, hour, or any alias for those units supported by PostgreSQL

Returns

Column	Type	Description
integral	DOUBLE PRECISION	The time-weighted integral for the interval (start, start + interval), computed from the TimeWeightSummary plus end points interpolated from prev and next