计算公开票的通过服务时间（Oracle SQL）

您可以计算出时间量：

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_secondsFROM   table_name);

其中，对于示例数据：

CREATE TABLE table_name ( Ticket_Nr, date_logged, date_closed ) ASSELECt 1234567, DATE '2021-01-06' + INTERVAL '11:30:52' HOUR TO SECOND, NULL FROM DUAL UNIOn ALLSELECt 8912345, DATE '2021-01-13' + INTERVAL '09:14:16' HOUR TO SECOND, NULL FROM DUAL UNIOn ALLSELECt 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 ALLSELECt       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 ALLSELECt       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 ALLSELECt       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 ALLSELECt       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
<>在这里拨弄