Skip to main content
Since 1.11.0 Downsample your data with the ASAP smoothing algorithm. This algorithm preserves the approximate shape and larger trends of the input data, while minimizing the local variance between points.

​
Arguments

NameTypeDefaultRequiredDescription
tsTIMESTAMPTZ-✔Timestamps for each data point
valueDOUBLE PRECISION-✔The value at each timestamp
resolutionINT-✔The approximate number of points to return. Determines the horizontal resolution of the resulting graph.

​
Returns

ColumnTypeDescription
asap_smoothTimevectorAn object representing a series of values occurring at set intervals from a starting time. It can be unpacked with unnest. For more information, see the documentation on timevectors.

​
Samples

This example uses a table called metrics, with columns for date and reading. The columns contain measurements that have been accumulated over a large interval of time. This example takes that data and provides a smoothed representation of approximately 10 points, but that still shows any anomalous readings: 
SET TIME ZONE 'UTC';
CREATE TABLE metrics(date TIMESTAMPTZ, reading DOUBLE PRECISION);
INSERT INTO metrics
SELECT
    '2020-1-1 UTC'::timestamptz + make_interval(hours=>foo),
    (5 + 5 * sin(foo / 12.0 * PI()))
    FROM generate_series(1,168) foo;

SELECT * FROM unnest(
  (SELECT asap_smooth(date, reading, 8)
    FROM metrics)
);

time                    |        value
------------------------+---------------------
2020-01-01 01:00:00+00  | 5.3664814565722665
2020-01-01 21:00:00+00  |  5.949469264090644
2020-01-02 17:00:00+00  |  5.582987807518377
2020-01-03 13:00:00+00  |  4.633518543427733
2020-01-04 09:00:00+00  |  4.050530735909357
2020-01-05 05:00:00+00  |  4.417012192481623
2020-01-06 01:00:00+00  |  5.366481456572268
2020-01-06 21:00:00+00  |  5.949469264090643