search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
16.82k stars 852 forks source link

How to perform linear interpolation? #7028

Open nieyuan1980 opened 3 weeks ago

nieyuan1980 commented 3 weeks ago

What type of enhancement is this?

API improvement

What subsystems and features will be improved?

Gapfill

What does the enhancement do?

There is a water meter, and the collector records the reading every hour. The data is as follows:

捕获1

It can be seen that there is missing data at 06:00, 07:00, 08:00, and 09:00. How to linearly interpolate the missing data?

Implementation challenges

No response

melicheradam commented 2 weeks ago

Have you tried this? https://docs.timescale.com/api/latest/hyperfunctions/gapfilling/time_bucket_gapfill/

Straight from the docs

SELECT time_bucket_gapfill('1 hour', time) AS time,
    interpolate(max(value)) as max_reading
    GROUP BY time
    ORDER BY time;