Hi all,
I have a system that produces time series data under normal operation. There are multiple instruments, each of which can collect multiple parameters. I am ingesting the data in a self-hosted TimescaleDB instance using sparsely populated tables - each instrument gets its own table, columns for each parameter. Every row has a timestamp and then any number of filled rows.
I have a task to produce a timestamp synchronised subset of data on 5 second intervals. This is replacing a (very) end-of-life Java program. The functionality that I’m trying to duplicate is basically a single table that has 5s timestamps with interpolated values. The longest period between live values that the system should interpolate between is 120 seconds.
I am using an intermediate step - each table has a continuous aggregate of the merged data. I had implemented this as a mean-average over the 5s sample
CREATE MATERIALIZED VIEW merged.pco2 WITH (timescaledb.continuous) AS
SELECT time_bucket(‘5’, timestamp) AS bucket,
COALESCE(AVG(condensertemperature::double precision), -999.9) AS condensertemperature,
COALESCE(AVG(equilibratorpressure::double precision), -999.9) AS equilibratorpressure,
COALESCE(AVG(equilibratortemperature::double precision), -999.9) AS equilibratortemperature,
COALESCE(AVG(labpressure::double precision), -999.9) AS labpressure,
COALESCE(AVG(licorflow::double precision), -999.9) AS licorflow,
COALESCE(AVG(ventflow::double precision), -999.9) AS ventflow,
COALESCE(AVG(waterflow::double precision), -999.9) AS waterflow,
COALESCE(AVG(watervapour::double precision), -999.9) AS watervapour,
COALESCE(AVG(xco2::double precision), -999.9) AS xco2
FROM pco2
GROUP BY bucket WITH NO DATA;
but this does not handle the low frequency data (eg waterflow is received once every 60s).
Now I am trying to implement time_bucket_gapfill with interpolate. The idea that I’ve just had is to use gapfilling when I produce the data product specifically for a request.
Can anyone comment on whether this is a sane use of TimescaleDB? Are there tools that would make my life easier?
Also interested in techniques for enforcing the two minute bounds around 5s sample interpolation.
Kind regards,
Angus