WITH parms (entity_id, start_date, end_date) AS
(
    SELECT 
  		entity_id,
        time_c::timestamp,
       next_time_c::timestamp
    FROM 
        test_c
), weekend_days (entity_id, wkend) AS 
(
    SELECT 
		entity_id,
        SUM(case when extract(isodow from d) in (6, 7) then 1 else 0 end) 
    FROM 
        parms
    CROSS JOIN 
        generate_series(start_date, end_date, interval '1 day') dn(d)
	GROUP BY entity_id
)
SELECT 
	entity_id AS "ID",
    CONCAT(
        extract(day from diff), ' days ', 
        extract( hours from diff)   , ' hours ', 
        extract( minutes from diff) , ' minutes ', 
        extract( seconds from diff)::int , ' seconds '
    ) AS "Duration (excluding saturday and sunday)",
    justify_interval(end_date::timestamp - start_date::timestamp) AS "Duration full"
FROM (
    SELECT 
  		start_date,
  		end_date,
  		entity_id,
        (end_date-start_date) - (wkend * interval '1 day') AS diff
    FROM parms 
    JOIN weekend_days USING(entity_id)
) sq;

WITH temp2 AS (
    SELECT
        entity_id,
        old_phase,
        phase,
        time,
        next_time,
        to_timestamp(to_char(to_timestamp("time"/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris' AS "TIME 2",
        to_timestamp(to_char(to_timestamp("next_time"/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris'AS "NEXT TIME 2",
        ((next_time - time)/1000.0) AS DIFF
    FROM 
        tbl_history
    WHERE phase = 'Fulfill'
),parms (entity_id, start_date, end_date) AS
(
    SELECT 
        entity_id,
        "TIME 2"::timestamp,
        "NEXT TIME 2"::timestamp
    FROM 
        temp2
), weekend_days (entity_id, wkend) AS 
(
    SELECT 
		entity_id,
        SUM(case when extract(isodow from d) in (6, 7) then 1 else 0 end) 
    FROM 
        parms
    CROSS JOIN 
        generate_series(start_date, end_date, interval '1 day') dn(d)
	GROUP BY entity_id
)
SELECT 
	entity_id AS "ID",
    CONCAT(
        extract(day from diff), ' days ', 
        extract( hours from diff)   , ' hours ', 
        extract( minutes from diff) , ' minutes ', 
        extract( seconds from diff)::int , ' seconds '
    ) AS "Duration (excluding saturday and sunday)",
    justify_interval(end_date::timestamp - start_date::timestamp) AS "Duration full"
FROM (
    SELECT 
  		start_date,
  		end_date,
  		entity_id,
        (end_date-start_date) - (wkend * interval '1 day') AS diff
    FROM parms 
    JOIN weekend_days USING(entity_id)
) sq;

CREATE TABLE tbl_history (
	entity_id INT,
  	phase VARCHAR(255),
  	old_phase VARCHAR(255), 
  	time BIGINT, 
  	next_time BIGINT
);

INSERT INTO tbl_history VALUES
(1, 'Log', null, 1654781946989, 1654781949732),
(1, 'Approve', 'Log', 1654781949732, 1654781952676),
(1, 'Fulfill', 'Approve', 1654781952676, 1677506971778),
(1, 'Accept', 'Fulfill', 1677506971778, 1677518742552),
(1, 'Review', 'Accept', 1677518742552, 1678097845979),
(1, 'Fulfill', 'Review', 1678097845979, 1678097847325),
(1, 'Accept', 'Fulfill', 1678097847325, 1678097977816),
(1, 'Review', 'Accept', 1678097977816, null);

CREATE TABLE test_c (
	entity_id INT,
  	time_c timestamp ,
  	next_time_c timestamp 
);

INSERT INTO test_c VALUES
(1, '2022-06-09 15:39:00', '2023-02-27 15:09:48')
,(2, '2023-03-06 11:17:12', '2023-03-06 11:17:14')

SELECT
    t.entity_id
  , t.phase
  , t.old_phase
  , ts.bus_days
  , bd.bus_interval
  , justify_interval(bd.bus_interval) as alt_interval
  , bd.interval_24x7
  , ts.start_ts
  , ts.end_ts
  , t.time
  , t.next_time
FROM tbl_history AS t
inner join lateral (
  /* this lateral join "extends" the source row with these columns */
  SELECT 
       to_timestamp(t.time / 1000)::timestamp      AS start_ts
     , to_timestamp(t.next_time / 1000)::timestamp AS end_ts
     , (SELECT COUNT(*)::int 
        FROM generate_series(to_timestamp(t.time / 1000)::DATE, to_timestamp(t.next_time / 1000)::DATE, '1 day')
        WHERE EXTRACT(ISODOW FROM generate_series) < 6
        ) AS bus_days
  ) AS ts ON true
inner join lateral (
  /* additional lateral join "further extends" the row with these columns 
     and it can access the columns produced by earlier lateral joins
  */
  SELECT 
    ts.end_ts - ts.start_ts as interval_24x7
  , make_interval(0,0,0,ts.bus_days - 1) + ((ts.end_ts - ts.start_ts)::time)::interval  as bus_interval
  ) as bd on true

CREATE OR REPLACE FUNCTION business_interval(start_time BIGINT, end_time BIGINT)
RETURNS INTERVAL AS $$
DECLARE
    bus_days INTEGER;
    interval_24x7 INTERVAL;
BEGIN
    bus_days := (SELECT COUNT(*) FROM generate_series(
                         to_timestamp(start_time / 1000)::date
                       , to_timestamp(end_time / 1000)::date
                       , '1 day') WHERE EXTRACT(ISODOW FROM generate_series) < 6);
    interval_24x7 := to_timestamp(end_time / 1000)::timestamp 
                   - to_timestamp(start_time / 1000)::timestamp;
    RETURN make_interval(days := bus_days-1) + ((interval_24x7::time)::interval);
  
END;
$$ LANGUAGE plpgsql;

select
      business_interval(time,next_time) as bus_interval
    , *
from tbl_history