asap_smooth.md

api_name	excerpt	topics	api	hyperfunction	api_details
asap_smooth()	Downsample a time series using the ASAP smoothing algorithm	hyperfunctions	license type toolkit version community function true experimental stable 0.2.0 1.11.0	family type downsampling function	summary signatures parameters examples Downsample your data with the [ASAP smoothing algorithm](https://arxiv.org/pdf/1703.00983.pdf). This algorithm preserves the approximate shape and larger trends of the input data, while minimizing the local variance between points. language code sql asap_smooth( ts TIMESTAMPTZ, value DOUBLE PRECISION, resolution INT ) RETURNS Timevector required returns name type description ts TIMESTAMPTZ Timestamps for each data point name type description value DOUBLE PRECISION The value at each timestamp name type description resolution INT The approximate number of points to return. Determines the horizontal resolution of the resulting graph. column type description asap_smooth Timevector An 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](/use-timescale/latest/hyperfunctions/function-pipelines/#timevectors). description command return 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: code 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) ); code 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