I am using
- PostgreSQL 13.4
- TimescaleDB 2.4.0
I have created a hypertable called measurements
CREATE TABLE public.measurements
(time timestamptz NOT NULL,machine text NOT NULL,alarm_msg text NULL);
Sample data 1:
| Time | Machine | alarm_msg |
| -----| ------- | ------- |
| 2021-09-23 11:59:40.0 +0000 | Device1 ||
| 2021-09-23 11:59:41.0 +0000 | Device1 |alarm1|
| 2021-09-23 11:59:42.0 +0000 | Device1 |alarm1|
| 2021-09-23 11:59:43.0 +0000 | Device1 ||
| 2021-09-23 11:59:44.0 +0000 | Device1 |alarm2|
| 2021-09-23 11:59:45.0 +0000 | Device1 |alarm2|
| 2021-09-23 11:59:46.0 +0000 | Device1 |alarm2|
| 2021-09-23 11:59:47.0 +0000 | Device1 |alarm2|
| 2021-09-23 11:59:48.0 +0000 | Device1 ||
| 2021-09-23 11:59:49.0 +0000 | Device1 ||
Sample data 2
| Time | Machine | alarm_msg |
| -----| ------- | ------- |
| 2021-09-23 11:59:50.0 +0000 | Device1 |alarm1|
| 2021-09-23 11:59:50.0 +0000 | Device2 |alarm4|
| 2021-09-23 11:59:51.0 +0000 | Device1 |alarm1|
| 2021-09-23 11:59:51.0 +0000 | Device2 |alarm4|
| 2021-09-23 11:59:52.0 +0000 | Device1 ||
| 2021-09-23 11:59:52.0 +0000 | Device2 |alarm4|
| 2021-09-23 11:59:53.0 +0000 | Device1 ||
| 2021-09-23 11:59:53.0 +0000 | Device2 |alarm4|
| 2021-09-23 11:59:54.0 +0000 | Device1 ||
| 2021-09-23 11:59:54.0 +0000 | Device2 |alarm4|
Here I wanted to group alarms by duration for each machine.
Expected result should be as below,
For Sample Data 1
| Generated On | Machine | alarm_msg | ended_on |
| -----| ------- | ------- |------- |
| 2021-09-23 11:59:41.0 +0000 | Device1 |alarm1| 2021-09-23 11:59:42.0 +0000|
| 2021-09-23 11:59:44.0 +0000 | Device1 |alarm2| 2021-09-23 11:59:47.0 +0000|
For Sample Data 2
| Generated On | Machine | alarm_msg | ended_on |
| -----| ------- | ------- |------- |
| 2021-09-23 11:59:50.0 +0000 | Device1 |alarm1|2021-09-23 11:59:51.0 +0000|
| 2021-09-23 11:59:50.0 +0000 | Device2 |alarm4|2021-09-23 11:59:54.0 +0000|
Notes.
- The data for all the machines is coming every second.
- Initially the generated on and ended_on will be same, as to track duration of ongoing alarm.
- Rather than writing a one time query, I want to use continues aggregate or triggers to continuously update (say alarms_history) table with aggregated data records.
For now I am using below query to get recent alarms records
WITH alarms_by_machines AS (
SELECT
time,
machine,
trim(alarm_msg) alarm_msg,
LEAD(alarm_msg) OVER (PARTITION BY machine ORDER BY time DESC) as previous
FROM measurements
WHERE
time >= <some recent time> AND
time < now()
),
alarms_aggregated AS (
SELECT
time as generated_on,
machine,
trim(alarm_msg) alarm_msg,
EXTRACT(EPOCH FROM (LAG(time) over (PARTITION BY machine ORDER BY time DESC)-time)) as duration
FROM alarms_by_machines
WHERE ( previous != alarm_msg or previous is null )
group by alarm_msg, time, machine
order by machine, time DESC
)
SELECT
*
FROM
alarms_aggregated
WHERE
alarms_aggregated.alarm_msg != ''
ORDER BY
alarms_aggregated.generated_on DESC LIMIT 10
I am still facing issues with above query, so that I want to store aggregates automatically.
So, I need something, where the different alarms would have their machine, alarm_message with generated_on and ended_on time ranges when each alarm occurred, for all the machines.
Any leads will be appreciated. Thanks in advance.
EDIT:
I tried creating a trigger, which will execute after insert and will record aggregates into another table named alarms_history,
INSERT INTO alarms_history ( machine, message, generated_on)
SELECT NEW.machine, NEW.alarm_msg, NEW.time
WHERE NOT EXISTS (SELECT 1 FROM alarms_history WHERE machine = NEW.machine and message = NEW.alarm_msg AND ended_on IS NULL);
At the moment, It only adds distinct machine,message into alarms_history but if a alarm comes again, it will skip(as ended_on is always null). If I can, somehow, calculate the ended_on, and update the ended_on in alarms_history, then it might work as expected.
Any leads/ideas to achieve this?