monitor, PostgreSQL

Language: Layout:

drop table if exists monitor cascade;

CREATE TABLE monitor(tz timestamptz, level int, table_name text, status text);

insert into monitor (tz, level, status, table_name)
values 
('2019-10-24 16:18:34.89435+05:30', 2,'start', 'test_table_2'),
('2019-10-24 16:18:58.922523+05:30', 2,'end', 'test_table_2'),
('2019-11-01 10:31:08.948459+05:30', 3,'start', 'test_table_3'),
('2019-11-01 10:41:22.863529+05:30', 3,'end', 'test_table_3'),
('2019-11-01 10:51:44.009129+05:30', 3,'start', 'test_table_3'),
('2019-11-01 12:35:23.280294+05:30', 3,'end', 'test_table_3');

with numbered as (
  select tz, level, table_name, status, 
         row_number() over (partition by table_name, status order by tz) as rn
  from monitor
)
select st.table_name, st.level, st.tz as start_time, et.tz as end_time
from numbered as st
  join numbered as et on st.table_name = et.table_name
                     and et.status = 'end'
                     and et.level = st.level
                     and et.rn = st.rn
where st.status = 'start'
order by st.table_name, st.level;


absolute service time: 0,54 sec