Versions:
asap_smooth() ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.ExperimentalExperimental features are not suitable for production environments. They are included under the TimescaleDB experimental schema. Click to learn more.
Toolkit
TimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.
Experimental
Experimental features are not suitable for production environments. They are included under the TimescaleDB experimental schema. Click to learn more.
The ASAP smoothing alogrithm is designed to create
human-readable graphs that preserve the rough shape and larger trends
of the input data, while minimizing the local variance between points.
The asap_smooth hyperfunction provides an implementation of this
algorithm that takes (timestamptz, double precision) data and returns
an ASAP smoothed timevector line.
Required arguments
| Name | Type | Description |
|---|---|---|
ts | TIMESTAMPTZ | Column of timestamps corresponding to the values to aggregate |
value | DOUBLE PRECISION | Column to aggregate |
resolution | INT | Approximate number of points to return. Intended to represent the horizontal resolution in which the aggregate is graphed. |
Returns
| Column | Type | Description |
|---|---|---|
normalizedtimevector | NormalizedTimevector | An object representing a series of values occurring at set intervals from a starting time. It can be unpacked via unnest. |
Sample usage
This example uses a table called metrics, with columns for date and
reading that 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 toolkit_experimental.unnest(
(SELECT toolkit_experimental.asap_smooth(date, reading, 8)
FROM metrics));The output for this query:
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.949469264090643Found an issue on this page?
Report an issue!