how to calculate overlap duration of two non-periodic time series + grouped

Question

I have two timeseries tables without fixed interval timestamps (the sample data is fixed to 10s just for simplicity). I can't use TimeScaleDB or others at the moment and need to calculate the duration of time when each timeseries of the device table is active at the same time as the check table, at 1 second precision for a 24hr period.

I've failed miserably trying to get something to work using generateSeries to resample the data to 1s and fill in the blanks, nothing is quite right, and when it looks like it is, postgres just disappears forever or pgadmin crashes.

If I were do program in a C type lanuguage I would want to have two cursors for the each table row, and loop through the entire table in one pass. Surely we can do that here?

Diagram of the problem:

Example table:

DeviceOnline

  Device Id      Start                  End

( 'Device_001', '2023-07-03 11:00:00', '2023-07-03 11:00:50' ), 
( 'Device_001', '2023-07-03 11:01:10', '2023-07-03 11:01:30' ),
( 'Device_002', '2023-07-03 11:00:10', '2023-07-03 11:00:30' ),
( 'Device_002', '2023-07-03 11:01:00', '2023-07-03 11:01:20' ),
( 'Device_003', '2023-07-03 11:00:01', '2023-07-03 11:01:59' ),
( 'Device_003', '2023-07-03 11:04:00', '2023-07-03 11:05:00' ),
( 'Device_004', '2023-07-03 11:00:00', '2023-07-03 11:00:10' ),
( 'Device_004', '2023-07-03 11:00:40', '2023-07-03 11:00:50' ),

ServerOnline

( '2023-07-03 10:55:00', '2023-07-03 10:59:00' ),
( '2023-07-03 11:00:20', '2023-07-03 11:00:40' ), 
( '2023-07-03 11:01:10', '2023-07-03 11:01:40' ),
( '2023-07-03 11:02:00', '2023-07-03 11:03:00' ),


Expected Output

Device      Duration

Device_001  40s
Device_002  20s
Device_003  50s
Device_004   0s

Test fiddle

Answer 1

The tsrange type makes this simple.

BTW, are you really using PostgreSQL 9.6?

select deviceid, 
       sum(upper(overlap) - lower(overlap)) as overlap_time
  from (select d.deviceid, 
               tsrange(d.starttime, d.endtime) 
                 * tsrange(s.starttime, s.endtime) as overlap 
          from devicestatus d
               join serverstatus s
                 on tsrange(d.starttime, d.endtime) 
                      && tsrange(s.starttime, s.endtime)) o
 group by deviceid;

Updated fiddle