Calculating a difference between consecutive rows using triggers

Annotation

In this article, we delve into a scenario where the seamless storage of sensor data in a database is coupled with the concurrent computation of the variance between successive measurements.

Problem statement

Sensor data serves as a temporal representation of metric states. An added layer of valuable information can be derived by quantifying the rate of change, or the "velocity," between consecutive readings. However, the computation of these differences, commonly referred to as deltas, can be resource-intensive when performed on demand for each sensor.

The proposal is to use the trigger mechanism during data insertion, making the computation of these deltas automatic and cost-effective. This also shifts the computation burden from the application layer to the database.

What is a trigger?

From Wiki A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

Example

Proceeding with our case, let's consider the following table:

CREATE TABLE sensor_data
(
    "timestamp"   INTEGER NOT NULL,
    "sensor_id"   INTEGER NOT NULL,
    "value"       FLOAT   NOT NULL
);

ALTER TABLE sensor_data ADD UNIQUE ("timestamp", "sensor_id");

With columns to store value and time deltas the creation script will look as follows:

CREATE TABLE sensor_data
(
    "timestamp" BIGINT  NOT NULL,
    "sensor_id" INTEGER NOT NULL,
    "value"     FLOAT   NOT NULL,
    "dv"        FLOAT,
    "dt"        BIGINT
);

ALTER TABLE sensor_data ADD UNIQUE ("timestamp", "sensor_id");

Creating a trigger

CREATE OR REPLACE FUNCTION calculate_deltas()
    RETURNS trigger AS
$$
DECLARE
    last_value FLOAT;
    last_ts    BIGINT;
    now        BIGINT;
BEGIN
    RAISE NOTICE 'NEW: %', NEW;

    SELECT "timestamp", value
    INTO last_ts, last_value
    FROM sensor_data t
    WHERE t.sensor_id = NEW.sensor_id
    ORDER BY "timestamp" DESC
    LIMIT 1;

    RAISE NOTICE 'last_ts = %; last_value = %', last_ts, last_value;

    IF FOUND THEN
        SELECT (EXTRACT(EPOCH FROM now()) * 1000)::BIGINT INTO now;
        NEW."timestamp" := now;
        NEW.dv := NEW.value - last_value;
        NEW.dt := now - last_ts;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER tr_sensor_data_deltas
    BEFORE INSERT
    ON sensor_data
    FOR EACH ROW
EXECUTE PROCEDURE calculate_deltas();

Showcase

Now let's insert a couple of values into sensor_data table.

INSERT INTO my_sensor_data ("timestamp", sensor_id, "value")
VALUES ((EXTRACT(EPOCH FROM now()) * 1000)::BIGINT, 1, 3.14);
-- wait a bit
INSERT INTO sensor_data("timestamp", sensor_id, "value") 
VALUES((EXTRACT(EPOCH FROM now()) * 1000)::int, 1, 2.74);

Now let's review the data in the table:

SELECT * FROM sensor_data;

Output should be like this

timestamp	sensor_id	value	dv	dt
1701109996686	1	2.74	-0.39	4814
1701109991872	1	3.14	NULL	NULL

The dv and dt columns were populated by the trigger automatically.