Calculating the change of a value versus the last reading in TimescaleDB

user_16758223 0
Viewed 537

I've got a load of time series data about a fleet of batteries stored in TimescaleDB, which record the 'state of charge' of each tank at each time. I don't have measurement of the in- and out-flow, only the instantaneous state of charge.

From this data, I want to find the change in state of charge at each time, which I will later bucket to consumption across hours (after doing some battery-specific maths).

I've written an SQL query which achieves my goal:

SELECT time, charge - LAG(charge) OVER (ORDER BY time) AS delta_soc FROM charge_data;

Putting that in a Postgres generated column:

ADD COLUMN delta_soc smallint GENERATED ALWAYS AS (charge - LAG(charge) OVER (ORDER BY time)) STORED

Fails, as promised in the docs, because it references another row.

So, I (successfully) made a materialized view:

CREATE MATERIALIZED VIEW delta_soc AS
SELECT
  time,
  batt_uid,
  charge,
  (charge-LAG(charge) OVER (ORDER BY time)) as delta_charge,
  EXTRACT(EPOCH FROM time-LAG(time) OVER (ORDER BY time)) as delta_time
FROM charge_data 
ORDER BY time;

But it would be nice to have this data in near-realtime. After all, it's a "simple" operation to just provide the change from last value. So, I looked at Timescale's continuous aggregates. But, as in the docs, you're not allowed a window function in a continuous aggregate, so the continuous aggregate is invalid.

Then, just throwing things at the wall and seeing what sticks, I wondered if I could reference the previous row during insertion

INSERT INTO charge_data VALUES (..., ([$chargevalue]-LAG(charge) OVER (ORDER BY time)), ...);
HINT:  There is a column named "charge" in table "mx_data", but it cannot be referenced from this part of the query.

I'm aware I could calculate the deltas

  • before insertion
  • after insertion by modifying each charge_data row with its delta
  • in the SQL query
  • in the querying program

But it seems much simpler and tidier to just have the DB calculate the values once at/around insertion, leading me to suspect I'm missing something. Is there any way to have charge[battery][n]-charge[battery][n-1] calculated and stored for every row in near-realtime in timescale?

timescaledb postgresql
1 Answers

I think a before insert trigger would work fine. You can make a before_insert trigger and update the delta soc while you're inserting using the previous reference.

CREATE TABLE batteries ( time timestamp not null, batt_uid varchar, charge int, delta int);
SELECT create_hypertable('batteries', 'time');

CREATE OR REPLACE FUNCTION update_delta() RETURNS trigger AS
$BODY$
DECLARE
    previous_charge integer; 
BEGIN
   select charge
   into previous_charge
   from batteries where batt_uid = NEW.batt_uid
   order by time desc limit 1;

  IF NEW.charge IS NOT NULL THEN
    IF previous_charge IS NOT NULL THEN
       NEW.delta = NEW.charge - previous_charge;
    ELSE
      NEW.delta = 0;

    END IF;
  END IF;

  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_delta_on_insert
               BEFORE INSERT
               ON batteries
               FOR EACH ROW
               EXECUTE PROCEDURE update_delta();

Testing


INSERT INTO batteries VALUES 
('2021-08-26 10:09:00'::timestamp, 'battery-1', 32),
('2021-08-26 10:09:01'::timestamp, 'battery-1', 34),
('2021-08-26 10:09:02'::timestamp, 'battery-1', 38);

INSERT INTO batteries VALUES 
('2021-08-26 10:09:00'::timestamp, 'battery-2', 0),
('2021-08-26 10:09:01'::timestamp, 'battery-2', 4),
('2021-08-26 10:09:02'::timestamp, 'battery-2', 28),
('2021-08-26 10:09:03'::timestamp, 'battery-2', 32),
('2021-08-26 10:09:04'::timestamp, 'battery-2', 28);

Output from:

SELECT * FROM batteries;

┌─────────────────────┬───────────┬────────┬───────┐
│        time         │ batt_uid  │ charge │ delta │
├─────────────────────┼───────────┼────────┼───────┤
│ 2021-08-26 10:09:00 │ battery-1 │     32 │     0 │
│ 2021-08-26 10:09:01 │ battery-1 │     34 │     2 │
│ 2021-08-26 10:09:02 │ battery-1 │     38 │     4 │
│ 2021-08-26 10:09:00 │ battery-2 │      0 │     0 │
│ 2021-08-26 10:09:01 │ battery-2 │      4 │     4 │
│ 2021-08-26 10:09:02 │ battery-2 │     28 │    24 │
│ 2021-08-26 10:09:03 │ battery-2 │     32 │     4 │
│ 2021-08-26 10:09:04 │ battery-2 │     28 │    -4 │
└─────────────────────┴───────────┴────────┴───────┘
user_24257030
Related