您可以计算出时间量：

SELECT ticket_nr,
       date_logged,
       current_datetime,
       date_closed,
       TO_CHAR( FLOOR( service_time_seconds / 60 / 60 ), 'FM9990' )
       || ':'
       || TO_CHAR( MOD( FLOOR( service_time_seconds / 60 ), 60 ), 'FM00' )
       || ':'
       || TO_CHAR( MOD( service_time_seconds, 60 ), 'FM00' )
         AS "SERVICE_TIME HH:MM:SS"
FROM   (
SELECT ticket_nr,
       date_logged,
       SYSDATE AS current_datetime,
       date_closed,
       ROUND(
         (
           -- Calculate the full weeks difference from the start of ISO weeks.
           ( 
             TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' )
             - TRUNC( date_logged, 'IW' )
           ) * (9.5*4+6)/(7*24)
           -- Add the hours for the full days for the final week.
           + DECODE(
               TRUNC( COALESCE( date_closed, SYSDATE ) )
               - TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
               0,  0.0,
               1,  9.5,
               2, 19.0,
               3, 28.5,
               4, 38.0,
                  44.0
             ) / 24
           -- Subtract the hours for the full days from the days of the week
           -- before the date logged.
           - DECODE(
               TRUNC( date_logged )
               - TRUNC( date_logged, 'IW' ),
               0,  0.0,
               1,  9.5,
               2, 19.0,
               3, 28.5,
               4, 38.0,
                  44.0
             ) / 24
           -- Add the hours of the final day
           + LEAST(
               GREATEST(
                 COALESCE( date_closed, SYSDATE )
                 - ( TRUNC( COALESCE( date_closed, SYSDATE ) )
                     + INTERVAL '07:00' HOUR TO MINUTE
                   ),
                 0
               ),
               DECODE(
                 TRUNC( COALESCE( date_closed, SYSDATE ) )
                 - TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
                 0, 9.5,
                 1, 9.5,
                 2, 9.5,
                 3, 9.5,
                 4, 6.0,
                    0.0
               ) / 24
             )
           -- Subtract the hours of the day before the range starts.
           - LEAST(
               GREATEST(
                 date_logged
                 - ( TRUNC( date_logged ) + INTERVAL '07:00' HOUR TO MINUTE ),
                 0
               ),
               DECODE(
                 TRUNC( date_logged )
                 - TRUNC( date_logged, 'IW' ),
                 0, 9.5,
                 1, 9.5,
                 2, 9.5,
                 3, 9.5,
                 4, 6.0,
                    0.0
               ) / 24
             )
         )
         -- Multiply to give seconds rather than fractions of full days.
         * 24 * 60 * 60
       ) AS service_time_seconds
FROM   table_name
);

其中，对于示例数据：

CREATE TABLE table_name ( Ticket_Nr, date_logged, date_closed ) AS
SELECT 1234567, DATE '2021-01-06' + INTERVAL '11:30:52' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 8912345, DATE '2021-01-13' + INTERVAL '09:14:16' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 6789012, DATE '2021-01-14' + INTERVAL '10:48:28' HOUR TO SECOND, DATE '2021-01-21' + INTERVAL '11:40:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       1, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-14' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       2, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       3, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       4, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-10' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL

输出（哪里NLS_DATE_FORMAT是YYYY-MM-DD HH24:MI:SS (DY)）：

TICKET_NR | DATE_LOGGED | CURRENT_DATETIME | DATE_CLOSED | SERVICE_TIME

HH：MM：SS
--------：| ：------------------------ | ：------------------------ |
：------------------------ | ：--------------------
1234567 | 2021-01-06 11:30:52（星期三）| 2021-01-14 12:36:54（THU）| 空
| 54:36:02
8912345 | 2021-01-13 09:14:16（星期三）| 2021-01-14 12:36:54（THU）| 空
| 12:52:38
6789012 | 2021-01-14 10:48:28（THU）| 2021-01-14 12:36:54（THU）|
2021-01-21 11:40:00（THU）| 44:51:32
1 | 2021-01-07 07:00:00（THU）| 2021-01-14 12:36:54（THU）|
2021-01-14 07:00:00（THU）| 44:00:00
2 | 2021-01-07 07:00:00（THU）| 2021-01-14 12:36:54（THU）|
2021-01-08 07:00:00（FRI）| 9:30:00
3 | 2021-01-08 07:00:00（FRI）| 2021-01-14 12:36:54（THU）|
2021-01-09 07:00:00（SAT）| 6:00:00
4 | 2021-01-09 07:00:00（SAT）| 2021-01-14 12:36:54（THU）|
2021-01-10 07:00:00（SUN）| 0:00:00

db
<>在这里拨弄