timescaledb code example

mine_data_gapfill.sql

CREATE TABLE mine_data
(
  serial TEXT,
  timestamp TIMESTAMPTZ,
  values JSONB NOT NULL DEFAULT '{}',
  PRIMARY KEY (serial, timestamp)
);
SELECT CREATE_HYPERTABLE('mine_data', 'timestamp');

INSERT INTO mine_data (serial, timestamp, values) VALUES
('123', '2020-01-01', '{"a": 1, "b": 1, "c": 1}'),
('123', '2020-01-02', '{        "b": 2, "c": 2}'),
('123', '2020-01-03', '{"a": 3,         "c": 3}'),
('123', '2020-01-04', '{"a": 4, "b": 4        }'),
('123', '2020-01-06', '{"a": 6, "b": 6, "c": 6}');


CREATE FUNCTION mine_data_gapfill(serial TEXT, start_date TIMESTAMPTZ, end_date TIMESTAMPTZ, time_bucket INTERVAL = '1 DAY', locf_prev INTERVAL = '1 DAY')
RETURNS SETOF mine_data AS $$
  SELECT $1, ts, JSONB_OBJECT_AGG(key_name, gapfilled)
  FROM (
    SELECT
      serial,
      TIME_BUCKET_GAPFILL(time_bucket, MT.timestamp) AS ts,
      jsondata.key AS key_name,
      LOCF(AVG((jsondata.value)::REAL)::REAL, treat_null_as_missing:=TRUE, prev:=(
        SELECT (values->>jsondata.key)::REAL
        FROM mine_data
        WHERE values->>jsondata.key IS NOT NULL AND serial = $1
        AND timestamp < start_date AND timestamp >= start_date - locf_prev
        ORDER BY timestamp DESC LIMIT 1
      )) AS gapfilled
    FROM mine_data MT, JSONB_EACH(MT.values) AS jsondata
    WHERE MT.serial = $1 AND MT.timestamp >= start_date AND MT.timestamp <= end_date
    GROUP BY ts, jsondata.key, serial
    ORDER BY ts ASC, jsondata.key ASC
  ) sourcedata
  GROUP BY ts, serial
  ORDER BY ts ASC;
$$ LANGUAGE SQL STABLE;

SELECT * FROM mine_data_gapfill('123', '2020-01-01', '2020-01-06', '1 DAY');