CODEWITHSUNDEEP
databasepostgresqltime-seriestimescaledb
Pranas Zeromskas
Pranas Zeromskas

Reputation: 1

Cumulative sensor aggregation handling after reset/rollover

I am creating a db for storing sensor data. i have a hypertable for raw readings and continuous aggregate calculating 1 hour aggregations. It is all working well for sensors that are using e.g. average aggregation but now I am lost and don't know what to do when cumulative sensors (diff aggregation) resets (changed to new or rolled over). In continuous aggregate I will have one bucket that is incorrect (negative or very large value) and I won't be able fix it by altering row data. Do you have any suggestions that would solve this problem for me?

Current:

CREATE TABLE sensor_data
(
    sensor_id    INTEGER                  NOT NULL,
    timestamp    TIMESTAMP WITH TIME ZONE NOT NULL,
    value        NUMERIC(18, 6)
);

SELECT timescale.create_hypertable('sensor_data', 'timestamp');
SELECT timescale.add_dimension('sensor_data', 'sensor_id', chunk_time_interval => 1);

CREATE MATERIALIZED VIEW sensor_data_hourly
    WITH (timescaledb.continuous) AS
        SELECT sensor_id,
               timescale.time_bucket('1 hour', timestamp) AS bucket,
               SUM(value)                       AS sum,
               AVG(value)                       AS avg,
               MAX(value)                       AS max,
               MIN(value)                       AS min,
               COUNT(value)                     AS count,
               -- would probably be more accurate with the lag function,
               -- but since materialized view doesn't support it
               -- (correct me if i'm wrong) I am working with this
               MAX(value) - MIN(value)          AS diff
        FROM sensor_data
        GROUP BY 1, 2
        ORDER BY 1, 2;

Possible solutions I could think of:

  1. Adding column offset or sensor_value+offset and calculate it for new inserts of cumulative aggregation type
  2. Creating a table for cumulative sensors reset info (sensor_id, time, value before reset, value after reset) and add this to continuous aggregate calculation or during retrieval of data from this continuous aggregate.

Are these solutions any good (if yes, which) or is there a better and more optimal way?

Upvotes: 0

Views: 31

Answers (0)

Related Questions