interpolated_integral()

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

Compute a time-weighted integral over the interval, defined as start plus interval, given a prev and next time-weight summary from which to compute the boundary points. This is intended to allow a precise time-weighted integral over intervals even when the points representing the intervals are grouped into discrete time-weight summaries. PostgreSQL window functions such as LEAD and LAG can be used to determine the prev and next arguments, as used in the examples in this section.

Note that if either prev or next are NULL, the first or last point in the summary is treated as the edge of the interval. The interpolated point is determined using LOCF (Last Observation Carries Forward) or linear interpolation, depending on which interpolation style the time-weight summary was created with.

This function is similar to interpolated_average but doesn't divide by the length of time being integrated.

Required arguments

NameTypeDescription
twsTimeWeightSummaryThe input TimeWeightSummary from a time_weight call
startTIMESTAMPTZThe start of the interval which the time-weighted integral should cover (if there is a preceeding point)
intervalINTERVALThe length of the interval which the time-weighted integral should cover

Optional arguments

NameTypeDescription
prevTimeWeightSummaryThe TimeWeightSummary from the prior interval, used to interpolate the value at start. If NULL, the first timestamp in tws is used as the start of the interval.
nextTimeWeightSummaryThe TimeWeightSummary from the following interval, used to interpolate the value at start + interval. If NULL, the last timestamp in tws is used as the end of the interval.
unitTEXTThe unit of time to express the integral in. Can be microsecond/millisecond/second/minute/hour or any alias for those units supported by PostgreSQL. If NULL, defaults to second.

Returns

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

Sample usage

-- 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
toolkit_experimental.interpolated_integral(
time_weight('LOCF', ts, storage_bytes),
'01-01-2022',
'5 days',
NULL,
NULL,
'hours'
)
FROM
user_storage_usage;

Found an issue on this page?

Report an issue!

Related Content