Hello,
i am right now evaluating Timescale for out use case and got the following question.
I am storing many events in the form of
time, key=“A”,value=1
time, key=“A”,value=0
time, key=“B”,value=1
time, key=“B”,value=0
I now want to aggregate the data in form, that I have the times between the event (key=“A” and value=1) and the event (key=“B” and value=1).
Is this possible within the DB with a SQL query?
I then would like to store this as a Continuous aggregates.
Thank You
Welcome @Tesla2k ,
Yes! You can do it with a sql query. Let’s run some small example here:
CREATE TABLE events (
time TIMESTAMPTZ NOT NULL,
key TEXT NOT NULL,
value INT NOT NULL
);
SELECT create_hypertable('events', 'time');
Feeding some data:
INSERT INTO events (time, key, value) VALUES
('2023-04-28 08:00:00', 'A', 1),
('2023-04-28 08:10:00', 'A', 0),
('2023-04-28 08:15:00', 'B', 1),
('2023-04-28 08:25:00', 'B', 0);
Now, let’s query the data thinking that we just need to separate the states into sub relations:
WITH a_events AS (
SELECT time
FROM events
WHERE key = 'A' AND value = 1
),
b_events AS (
SELECT time
FROM events
WHERE key = 'B' AND value = 1
)
SELECT a.time AS a_time, b.time AS b_time, b.time - a.time AS time_difference
FROM a_events a, b_events b
WHERE a.time < b.time
ORDER BY a.time, b.time;
It would not work very well with continuous aggregates due to some limitations related to window functions. But you can implement the same behavior with an external table. As you know, continuous aggregates is a table too.
First you need to create a function to calculate the time difference:
CREATE OR REPLACE FUNCTION min_time_diff(a_time TIMESTAMPTZ, b_time TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
min_diff TIMESTAMPTZ;
BEGIN
IF a_time IS NULL OR b_time IS NULL THEN
RETURN NULL;
END IF;
SELECT MIN(b.time - a.time) INTO min_diff
FROM (SELECT time FROM events WHERE key = 'A' AND value = 1 AND time <= b_time) a,
(SELECT time FROM events WHERE key = 'B' AND value = 1 AND time >= a_time) b
WHERE a.time < b.time;
RETURN min_diff;
END;
$$ LANGUAGE plpgsql;
Then create the table that will track the differences:
CREATE TABLE time_differences (
time_bucket TIMESTAMPTZ NOT NULL UNIQUE,
min_time_difference INTERVAL
);
Now, the trigger function that will hook the updates:
```sql
CREATE OR REPLACE FUNCTION update_time_differences()
RETURNS TRIGGER AS $$
DECLARE
a_time TIMESTAMPTZ;
b_time TIMESTAMPTZ;
min_diff INTERVAL;
BEGIN
IF NEW.key = 'B' AND NEW.value = 1 THEN
SELECT time INTO a_time
FROM events
WHERE key = 'A' AND value = 1 AND time < NEW.time
ORDER BY time DESC
LIMIT 1;
IF a_time IS NOT NULL THEN
min_diff := NEW.time - a_time;
INSERT INTO time_differences (time_bucket, min_time_difference)
VALUES (time_bucket(INTERVAL '1 minute', NEW.time), min_diff)
ON CONFLICT (time_bucket) DO UPDATE
SET min_time_difference = LEAST(time_differences.min_time_difference, EXCLUDED.min_time_difference);
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_time_differences_trigger
AFTER INSERT ON events
FOR EACH ROW
EXECUTE FUNCTION update_time_differences();
I hope it helps, and if you decide to create time_differences as an hypertable, that is just an extra call to it and you also will get automatic partitioning 
Thank you very much. It worked.
I did not even need the function min_time_diff.