How to get the time difference of first occurrence of a record with another record

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

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';

Advertisement

Answer