We have a monitoring system that posts data to our postgres database in this format:
Our goal is to calculate the time between wa-event-triggered
events and wa-event-recovered
events, if i have data that looks like this, my query below works just fine.
ts | text
-----------------------------+------------------+
2021-04-15T18:35:08.000000Z | wa-event-triggered
2021-04-15T18:45:08.000000Z | wa-event-recovered
2021-04-15T18:50:08.000000Z | wa-event-triggered
2021-04-15T18:55:08.000000Z | wa-event-recovered
select *
from (
select text, ts,
CAST(ts as timestamp) - lag(CAST(ts as timestamp)) over (order by CAST(ts as timestamp)) as increase
from messages
where text ~ 'wa-event-'
) as alerts
where alerts.text ~ 'recovered'
;
This gives me this table which is great!
ts | text | Increase
-----------------------------+--------------------+-----------------+
2021-04-15T18:45:08.000000Z | wa-event-recovered | 00:10:00
2021-04-15T18:55:08.000000Z | wa-event-recovered | 00:05:00
However the data is not always sequential in that our alerting system can post multiple wa-event-triggered
events before we finally have a wa-event-recovered
event, so how would i get the time difference between the first wa-event-triggered
found and the first wa-event-recovered
found and ignore all the wa-event-triggered
events in between given that there will be hundreds of these triggered/recovered pairs.
ts | text
-----------------------------+------------------+
2021-04-15T18:35:08.000000Z | wa-event-triggered <- start timer
2021-04-15T18:45:08.000000Z | wa-event-triggered
2021-04-15T18:55:08.000000Z | wa-event-triggered
2021-04-15T18:50:08.000000Z | wa-event-recovered <- end timer
2021-04-15T19:00:00.000000Z | wa-event-triggered <- start timer
2021-04-15T19:05:08.000000Z | wa-event-triggered
2021-04-15T19:10:08.000000Z | wa-event-triggered
2021-04-15T19:15:08.000000Z | wa-event-recovered <- end timer
2021-04-15T20:05:08.000000Z | wa-event-triggered <- start timer
2021-04-15T20:10:08.000000Z | wa-event-triggered
2021-04-15T20:15:08.000000Z | wa-event-recovered <- end timer
Advertisement
Answer
You can just include the changed records and use your method:
select *
from (select text, ts,
ts::timestamp - lag(ts::timestamp) over (order by ts::timestamp) as increase
from (select m.*,
lag(text) over (order by ts::timestamp) as prev_text
from messages m
where text ~ 'wa-event-' or alerts.text ~ 'recovered'
) m
where text ~ 'wa-event-' and
prev_text is distinct from text
) alerts
where alerts.text ~ 'recovered';