Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE time_records (
  id          int NOT NULL
, employee_id int NOT NULL
, starttime   timestamp NOT NULL
, endtime     timestamp NOT NULL
);
   
INSERT INTO time_records VALUES
  (1, 1, '2023-09-01 07:00:00', '2023-09-01 09:00:00')
, (2, 1, '2023-09-01 07:00:00', '2023-09-01 15:00:00')
, (3, 1, '2023-09-01 07:00:00', '2023-09-01 15:00:00')
, (4, 1, '2023-09-01 14:00:00', '2023-09-01 15:00:00')
, (5, 1, '2023-09-01 23:45:00', '2023-09-01 23:59:00')
, (6, 1, '2023-09-01 23:45:00', '2023-09-01 23:59:00')
;

CREATE TABLE



INSERT 0 6


SELECT unnest(range_agg(tsrange(starttime, endtime, '[]'))) AS merged_range
FROM   time_records;


merged_range


["2023-09-01 07:00:00","2023-09-01 15:00:00"]

["2023-09-01 23:45:00","2023-09-01 23:59:00"]



SELECT 2


-- Postgres 14+
SELECT employee_id, lower(merged) AS starttime, upper(merged) AS endtime
     , array_agg(t.id ORDER BY t.id) AS ids
FROM  (
   SELECT employee_id, unnest(range_agg(tsrange(starttime, endtime, '[]'))) AS merged
   FROM   time_records
   GROUP  BY employee_id
   ) r
JOIN   time_records t USING (employee_id)
WHERE  r.merged @> t.starttime
GROUP  BY r.employee_id, r.merged
ORDER  BY r.employee_id, r.merged;


employee_id
starttime
endtime
ids


1
2023-09-01 07:00:00
2023-09-01 15:00:00
{1,2,3,4}

1
2023-09-01 23:45:00
2023-09-01 23:59:00
{5,6}



SELECT 2


--  Postgres 14+: the same with a LATERAL subquery
SELECT r.employee_id, lower(r.merged) AS starttime, upper(r.merged) AS endtime, i.ids
FROM  (
   SELECT employee_id, unnest(range_agg(tsrange(starttime, endtime, '[]'))) AS merged
   FROM   time_records
   GROUP  BY employee_id
   ) r
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT t.id
      FROM   time_records t
      WHERE  t.employee_id = r.employee_id
      AND    t.starttime <@ r.merged
      ORDER  BY t.id
      )
   ) i (ids)
ORDER  BY r.employee_id, r.merged;


employee_id
starttime
endtime
ids


1
2023-09-01 07:00:00
2023-09-01 15:00:00
{1,2,3,4}

1
2023-09-01 23:45:00
2023-09-01 23:59:00
{5,6}



SELECT 2


-- Postgres 9.6
-- create once:
CREATE OR REPLACE FUNCTION public.f_merge_ranges()
  RETURNS TABLE (
    employee_id int
  , starttime timestamp
  , endtime timestamp
  , ids int[]
  )
  LANGUAGE plpgsql AS
$func$
DECLARE
   r record;  -- current row
BEGIN
   FOR r IN 
      SELECT t.id, t.employee_id, t.starttime, t.endtime
      FROM   time_records t
      ORDER  BY t.employee_id, t.starttime, t.endtime DESC, t.id  -- better take longer range first
   LOOP
      IF r.employee_id = employee_id THEN  -- works for null in first iteration
         IF r.starttime > endtime THEN
            RETURN NEXT;
            starttime   := r.starttime;
            endtime     := r.endtime;
            ids         := ARRAY[r.id];
         ELSE
            ids         := ids || r.id;
            IF r.endtime > endtime THEN
               endtime  := r.endtime;
            END IF;
         END IF;
      ELSE
         IF employee_id IS NOT NULL THEN  -- catch first iteration
            RETURN NEXT;
         END IF;
         employee_id := r.employee_id;
         starttime   := r.starttime;
         endtime     := r.endtime;
         ids         := ARRAY[r.id];
      END IF;
   END LOOP;

   -- return last row (if any)
   IF FOUND THEN
      RETURN NEXT;
   END IF;
END
$func$;

CREATE FUNCTION


SELECT * FROM public.f_merge_ranges();


employee_id
starttime
endtime
ids


1
2023-09-01 07:00:00
2023-09-01 15:00:00
{2,3,1,4}

1
2023-09-01 23:45:00
2023-09-01 23:59:00
{5,6}



SELECT 2
employee_id	starttime	endtime	ids
1	2023-09-01 07:00:00	2023-09-01 15:00:00	{1,2,3,4}
1	2023-09-01 23:45:00	2023-09-01 23:59:00	{5,6}
employee_id	starttime	endtime	ids
1	2023-09-01 07:00:00	2023-09-01 15:00:00	{2,3,1,4}
1	2023-09-01 23:45:00	2023-09-01 23:59:00	{5,6}