종료시간에서 지정된 시간을 빼면 되지 않을까요? 방법은 여러가지 있을것 같은데.. 제가 해본것 올립니다.

select timestampdiff(hour,'2021-03-01 22:00:00', edt) diff

from (select '2021-03-01 18:00:00' sdt, '2021-03-02 04:00:00'  edt) t

where sdt < '2021-03-01 22:00:00'

select timestampdiff(hour, concat(date_sub(date(edt) , interval 1 day), ' 22:00:00'), edt) diff

from (select '2021-03-01 18:00:00' sdt, '2021-03-02 04:00:00'  edt) t

where sdt < concat(date_sub(date(edt) , interval 1 day), ' 22:00:00')

WITH t AS
(
SELECT 1 idx, 'A' nm, '2021-03-01 18:00:00' sdt, '2021-03-02 04:30:00' edt
UNION ALL SELECT 2, 'A', '2021-03-04 05:30:00', '2021-03-04 23:30:00'
UNION ALL SELECT 3, 'B', '2021-03-04 09:00:00', '2021-03-04 18:00:00'
UNION ALL SELECT 4, 'C', '2021-03-01 18:00:00', '2021-03-02 08:00:00'
UNION ALL SELECT 5, 'D', '2021-03-01 23:00:00', '2021-03-02 08:00:00'
UNION ALL SELECT 6, 'E', '2021-02-28 23:00:00', '2021-03-02 08:00:00'
)
SELECT idx, nm, sdt, edt
     , ROUND(
       SUM(CASE WHEN x < y THEN TIME_TO_SEC(TIMEDIFF(y, x)) ELSE 0 END)
       /60/60, 2) hours
  FROM (SELECT idx, nm, sdt, edt
             , GREATEST(s, DATE(s) + INTERVAL seq-1 DAY + INTERVAL 0 hour) x
             , LEAST   (e, DATE(s) + INTERVAL seq-1 DAY + INTERVAL 8 hour) y
          FROM (SELECT idx, nm, sdt, edt
                     , sdt + INTERVAL 2 HOUR s
                     , edt + INTERVAL 2 HOUR e
                  FROM t
                ) a
           LEFT OUTER JOIN
                (SELECT 1 seq
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 ) b
             ON seq <= DATEDIFF(DATE(e), DATE(s)) + 1
        ) a
 GROUP BY idx, nm, sdt, edt
;

야간 22시~06시 로 계산하기가 까다로워 원래의 시간을 2시간 이동시켜 0시~8시를 야간으로 간주하는 방식입니다. 여러날 걸쳐 근무가 가능하다고 보고 일별로 행 복제 한 뒤 일별 0시~8시 사이 시간을 계산하여 합산하는 방식입니다.