Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table it220325a (
  machine text
, mchstat text
, statrange tsrange
);

insert into it220325a values
('CT-001', 'RUN', tsrange('2022-03-25 01:00:00', '2022-03-25 02:00:00)', '[)'));

create table it220325b (
  machine text
, mchstat text
, statrange tsrange
, exclude using gist (statrange WITH &&)
);

insert into it220325b values
('CT-001', 'VACUUM', tsrange('2022-03-25 01:10:00', '2022-03-25 01:20:00', '[)')), 
('CT-001', 'COATING', tsrange('2022-03-25 01:30:00', '2022-03-25 01:40:00', '[)')),
('CT-001', 'NONVACUUM', tsrange('2022-03-25 01:50:00', '2022-03-25 01:55:00', '[)'));
1 rows affected

3 rows affected

select *
  from it220325a;


machine
mchstat
statrange


CT-001
RUN
["2022-03-25 01:00:00","2022-03-25 02:00:00")


select *
  from it220325b;


machine
mchstat
statrange


CT-001
VACUUM
["2022-03-25 01:10:00","2022-03-25 01:20:00")

CT-001
COATING
["2022-03-25 01:30:00","2022-03-25 01:40:00")

CT-001
NONVACUUM
["2022-03-25 01:50:00","2022-03-25 01:55:00")


with t1(machine, rangeagg) as (
select machine
     , range_agg(statrange)
  from it220325b
 group by machine
)
select a.machine
     , 'PRE-RUN' as "type"
     , unnest(tsmultirange(a.statrange) - t1.rangeagg) as "statrange"
  from it220325a a
  join t1
    on a.machine = t1.machine
union all
select a.machine
     , 'RUN'
     , b.statrange
  from it220325a a
  join it220325b b
    on a.machine = b.machine
   and a.statrange @> b.statrange
 order by 1,3;


machine
type
statrange


CT-001
PRE-RUN
["2022-03-25 01:00:00","2022-03-25 01:10:00")

CT-001
RUN
["2022-03-25 01:10:00","2022-03-25 01:20:00")

CT-001
PRE-RUN
["2022-03-25 01:20:00","2022-03-25 01:30:00")

CT-001
RUN
["2022-03-25 01:30:00","2022-03-25 01:40:00")

CT-001
PRE-RUN
["2022-03-25 01:40:00","2022-03-25 01:50:00")

CT-001
RUN
["2022-03-25 01:50:00","2022-03-25 01:55:00")

CT-001
PRE-RUN
["2022-03-25 01:55:00","2022-03-25 02:00:00")

machine	mchstat	statrange
CT-001	VACUUM	["2022-03-25 01:10:00","2022-03-25 01:20:00")
CT-001	COATING	["2022-03-25 01:30:00","2022-03-25 01:40:00")
CT-001	NONVACUUM	["2022-03-25 01:50:00","2022-03-25 01:55:00")

machine	type	statrange
CT-001	PRE-RUN	["2022-03-25 01:00:00","2022-03-25 01:10:00")
CT-001	RUN	["2022-03-25 01:10:00","2022-03-25 01:20:00")
CT-001	PRE-RUN	["2022-03-25 01:20:00","2022-03-25 01:30:00")
CT-001	RUN	["2022-03-25 01:30:00","2022-03-25 01:40:00")
CT-001	PRE-RUN	["2022-03-25 01:40:00","2022-03-25 01:50:00")
CT-001	RUN	["2022-03-25 01:50:00","2022-03-25 01:55:00")
CT-001	PRE-RUN	["2022-03-25 01:55:00","2022-03-25 02:00:00")