Подсчитать количество минут для каждого часа

CREATE TABLE my_table(id serial,f_date DATE,f_start TIME,f_end TIME);
 
WITH time_ranges AS (
  SELECT
    id,
    f_date,
    f_start,
    f_end,
    date_trunc('hour', f_date + f_start)::TIME AS hour_start,
    (date_trunc('hour', f_date + f_end) +
     CASE WHEN (f_end > date_trunc('hour', f_date + f_end)::TIME)
          THEN INTERVAL '1 hour' ELSE INTERVAL '0' END)::TIME AS hour_end
  FROM my_table
),
expanded_hours AS (
  SELECT
    id,
    f_date,
    generate_series(
      (f_date + hour_start)::TIMESTAMP,
      (f_date + hour_end - INTERVAL '1 hour')::TIMESTAMP,
      INTERVAL '1 hour'
    )::TIME AS hour_slot,
    f_start,
    f_end,
    hour_start AS first_hour,
    hour_end - INTERVAL '1 hour' AS last_hour
  FROM time_ranges
)
SELECT
  id,
  f_date,
  hour_slot AS aligned_hour,
  hour_slot AS aligned_hour_start,
  (hour_slot + INTERVAL '1 hour')::TIME AS aligned_hour_end,
  CASE
    WHEN date_trunc('hour', f_date + f_start)::TIME = date_trunc('hour', f_date + f_end)::TIME THEN
      EXTRACT(MINUTE FROM f_end - f_start)::INTEGER
    WHEN hour_slot = date_trunc('hour', f_date + f_start)::TIME THEN
      60 - EXTRACT(MINUTE FROM f_start)::INTEGER
    WHEN hour_slot = (date_trunc('hour', f_date + f_end)::TIME) THEN
      EXTRACT(MINUTE FROM f_end)::INTEGER
    ELSE 60
  END AS minutes_used,
  f_start,
  f_end
FROM expanded_hours
ORDER BY id, f_date, hour_slot;

CREATE TABLE mytable (id INT, "date" DATE, START TIME WITHOUT TIME zone, "end" TIME WITHOUT TIME zone);
 
INSERT INTO mytable VALUES (325,'2025-05-21','15:43:00','16:04:00'),(326,'2025-05-21','09:15:00','09:28:00'),
  (327,'2025-05-21','08:01:00','08:24:00'),(328,'2025-05-21','08:32:00','08:47:00'),(329,'2025-05-21','10:05:00','10:55:00'),
  (330,'2025-05-21','08:17:00','09:34:00'),(331,'2025-05-28','09:38:00','11:01:00'),(332,'2025-05-28','12:10:00','14:32:00'),
  (333,'2025-05-19','12:18:00','13:40:00'),(334,'2025-05-19','22:18:00','01:40:00');

WITH RECURSIVE cte AS (
    SELECT id,DATE,START,"end",
        CASE WHEN EXTRACT(HOUR FROM "end")<EXTRACT(HOUR FROM START) THEN 24 ELSE 0 END hadd,
        EXTRACT(HOUR FROM START) hstart,DATE rdate,
        CASE WHEN EXTRACT(HOUR FROM START)=EXTRACT(HOUR FROM "end")
             THEN EXTRACT(MINUTE FROM "end")-EXTRACT(MINUTE FROM START) 
             ELSE 60-EXTRACT(MINUTE FROM START) END mstart
      FROM mytable
    UNION 
    SELECT m.id,m.date,m.start,m."end",cte.hadd,cte.hstart+1 hstart,
        CASE WHEN cte.hstart=23 THEN cte.rdate+INTEGER '1' ELSE cte.rdate END rdate,
        CASE WHEN cte.hstart+1<EXTRACT(HOUR FROM m."end")+cte.hadd THEN 60 ELSE EXTRACT(MINUTE FROM m."end") END AS mstart
      FROM mytable m JOIN cte ON 
           m.id=cte.id AND 
           ((cte.hadd=0 AND cte.hstart+1<=EXTRACT(HOUR FROM m."end") OR
            (cte.hadd=24 AND cte.hstart+1<=24+EXTRACT(HOUR FROM m."end"))))
  )
SELECT id,rdate DATE,make_time(hstart::INTEGER-CASE WHEN hstart>23 THEN 24 ELSE 0 END,0,0) START,
    make_time(hstart::INTEGER-CASE WHEN hstart>23 THEN 24 ELSE 0 END,0,0)+INTERVAL '1 hour' AS "end", 
    mstart minytes 
  FROM cte ORDER BY id,rdate,hstart;

           ((cte.hadd=0 AND cte.hstart+1<=EXTRACT(HOUR FROM m."end") OR
            (cte.hadd=24 AND cte.hstart+1<=24+EXTRACT(HOUR FROM m."end"))))

           ((cte.hadd=0 AND cte.hstart+1<=EXTRACT(HOUR FROM m."end")) OR
            (cte.hadd=24 AND cte.hstart+1<=24+EXTRACT(HOUR FROM m."end")))

WITH RECURSIVE hours AS (
    SELECT 
        id,
        DATE,
        START AS hour_start,
        LEAST(DATE_TRUNC('hour', START) + INTERVAL '1 hour', END) AS hour_end,
        EXTRACT(EPOCH FROM LEAST(DATE_TRUNC('hour', START) + INTERVAL '1 hour', END) - START) / 60 AS minutes
    FROM 
        time_records
    WHERE 
        START < END 
    UNION ALL
    SELECT 
        id,
        DATE,
        hour_start + INTERVAL '1 hour',
        hour_end,
        EXTRACT(EPOCH FROM LEAST(hour_end, hour_start + INTERVAL '1 hour') - hour_start) / 60 AS minutes
    FROM 
        hours
    WHERE 
        hour_start < hour_end
)
SELECT 
    id,
    DATE,
    to_char(hour_start, 'YYYY-MM-DD HH24:00') AS START,
    to_char(hour_start + INTERVAL '1 hour', 'YYYY-MM-DD HH24:00') AS END,
    SUM(minutes) AS minutes
FROM 
    hours
GROUP BY 
    id, DATE, hour_start
ORDER BY 
    id, hour_start;

SELECT 
  id, 
  mytable.date,
  dHour::TIME  AS START,
  (dhour + INTERVAL '1' HOUR)::TIME AS END,
  --Продолжительность часового периода
  (EXTRACT(epoch FROM  dEndPeriod - dStartPeriod) / 60)::INTEGER minutes
FROM 
  mytable,
  --Разбиваем период по часам
  generate_series(date_trunc('hour', DATE + mytable.start),DATE +  mytable.end,INTERVAL '1' HOUR) dHour,
  --Начало часового периода
  greatest(dHour, DATE + mytable.start) dStartPeriod,
  --Окончание часового периода
  least(dHour + INTERVAL '1' HOUR, DATE + mytable.end) dEndPeriod

-- сначала для каждого пользователя на каждый день получу отрезок его рабочего времении
WITH time_ranges AS (SELECT MIN(start_time) AS start_time, MAX(end_time) AS end_time, DATE, employee_id
FROM pps.my_temp
GROUP BY DATE, employee_id
ORDER BY DATE ASC),
 
--далее разобъём этот отрезок на гранулы в 5 минут
expanded_hours AS (
SELECT
    employee_id,
    DATE,
    generate_series(
    CASE WHEN EXTRACT(MINUTE FROM start_time)::INTEGER % 5 = 0 THEN (DATE + start_time) 
    ELSE (DATE + make_time(EXTRACT(HOUR FROM start_time)::INTEGER, 
                   (EXTRACT(MINUTE FROM start_time)::INTEGER - MOD(EXTRACT(MINUTE FROM start_time)::INTEGER, 5)),
                   0
                   )) 
    END,
    CASE WHEN EXTRACT(MINUTE FROM end_time)::INTEGER % 5 = 0 THEN (DATE + end_time) 
    ELSE (DATE + make_time(EXTRACT(HOUR FROM end_time)::INTEGER, 
                   ( CASE WHEN (EXTRACT(MINUTE FROM end_time)::INTEGER + MOD(EXTRACT(MINUTE FROM end_time)::INTEGER, 5)) > 59 THEN 59
                   ELSE (EXTRACT(MINUTE FROM end_time)::INTEGER + MOD(EXTRACT(MINUTE FROM end_time)::INTEGER, 5)) END ),
                   0
                   )) 
    END,
    INTERVAL '5 minute'
    )::TIME AS granula_start
  FROM time_ranges),
 
granules AS (
SELECT
    employee_id,
    DATE,
    granula_start,
    granula_start +  INTERVAL '5 minute' AS granula_end
  FROM expanded_hours)
--select * from granules order by date asc
--- теперь полученные гранулы заполним данными о приложении из таблицы pps.my_temp
SELECT 
employee_id,
DATE,
granula_start,
granula_end,
 
(SELECT 
    array_agg(json_build_object('seconds', SUM( CASE 
                                      WHEN end_time > granula_end 
                                      THEN ((EXTRACT(MINUTE FROM granula_end) - EXTRACT(MINUTE FROM start_time))*60) 
                                      ELSE (CASE  WHEN EXTRACT(MINUTE FROM start_time) = EXTRACT(MINUTE FROM end_time) 
                                                  THEN 
                                                   EXTRACT(SECOND FROM end_time) - EXTRACT(SECOND FROM start_time)
                                                  ELSE
                                                   (EXTRACT(MINUTE FROM end_time) - EXTRACT(MINUTE FROM start_time)) * 60 
                                           END )
                                      END ),
                      'domain_site', m.app_info::jsonb->'domain_site',
                      'app_name', m.app_info::jsonb->'app_name',
                      'app_type', m.app_info::jsonb->'app_type' )) AS app_info
 FROM pps.my_temp m
 WHERE start_time >= granula_start AND start_time <= granula_end
 GROUP BY m.app_info::jsonb->'domain_site', m.app_info::jsonb->'app_name', m.app_info::jsonb->'app_type'
 )
 
FROM granules