Is it possible to do sum of time interval-CodePudding

I have some data of time interval. Now I want to do sum from those time interval data. So how can I do that? Data are like as..

START_TIME	END_TIME	PROCESS_RUN_TIME
9/8/2022 8:45:20.631429 PM	9/8/2022 8:45:23.257592 PM	000000000 00:00:02.626163000
9/8/2022 8:45:24.211717 PM	9/8/2022 8:45:24.896982 PM	000000000 00:00:00.685265000
9/8/2022 8:45:25.414725 PM	9/8/2022 8:45:27.375699 PM	000000000 00:00:01.960974000
9/8/2022 8:45:34.608107 PM	9/8/2022 8:45:35.027469 PM	000000000 00:00:00.419362000
9/8/2022 8:45:35.341021 PM	9/8/2022 8:45:42.104194 PM	000000000 00:00:06.763173000
9/8/2022 8:50:43.314294 PM	9/8/2022 8:53:05.335864 PM	000000000 00:02:22.021570000

Here START_TIME and END_TIME are TIMESTAMP datatype and PROCESS_RUN_TIME is VARCHAR2 datatype.

I do following. But ORA-30076: invalid extract field for extract source message pop-up.

`SELECT   NUMTODSINTERVAL (SUM (EXTRACT (DAY FROM PROCESS_RUN_TIME)), 'DAY')
         NUMTODSINTERVAL (SUM (EXTRACT (HOUR FROM PROCESS_RUN_TIME)), 'HOUR')
         NUMTODSINTERVAL (SUM (EXTRACT (MINUTE FROM PROCESS_RUN_TIME)),
                          'MINUTE')
         NUMTODSINTERVAL (SUM (EXTRACT (SECOND FROM PROCESS_RUN_TIME)),
                          'SECOND')
          AS SUM_OF_TIME
  FROM CONV_PROCESS_RUN_TIME;`

Thanks in advance.

CodePudding user response：

Do not store the difference between two times as a VARCHAR2 data type, use an INTERVAL DAY TO SECOND data type (and in this case, it can be a virtual column):

CREATE TABLE conv_process_run_time (
  START_TIME       TIMESTAMP(6),
  END_TIME         TIMESTAMP(6),
  PROCESS_RUN_TIME INTERVAL DAY(3) TO SECOND(6)
                   GENERATED ALWAYS AS (END_TIME - START_TIME)
);

Then you can insert the sample data:

INSERT INTO conv_process_run_time (START_TIME, END_TIME)
SELECT TIMESTAMP '2022-08-09 20:45:20.631429', TIMESTAMP '2022-08-09 20:45:23.257592' FROM DUAL UNION ALL
SELECT TIMESTAMP '2022-08-09 20:45:24.211717', TIMESTAMP '2022-08-09 20:45:24.896982' FROM DUAL UNION ALL
SELECT TIMESTAMP '2022-08-09 20:45:25.414725', TIMESTAMP '2022-08-09 20:45:27.375699' FROM DUAL UNION ALL
SELECT TIMESTAMP '2022-08-09 20:45:34.608107', TIMESTAMP '2022-08-09 20:45:35.027469' FROM DUAL UNION ALL
SELECT TIMESTAMP '2022-08-09 20:45:35.341021', TIMESTAMP '2022-08-09 20:45:42.104194' FROM DUAL UNION ALL
SELECT TIMESTAMP '2022-08-09 20:50:43.314294', TIMESTAMP '2022-08-09 20:53:05.335864' FROM DUAL;

And your query works:

SELECT   NUMTODSINTERVAL (SUM (EXTRACT (DAY FROM PROCESS_RUN_TIME)), 'DAY')
         NUMTODSINTERVAL (SUM (EXTRACT (HOUR FROM PROCESS_RUN_TIME)), 'HOUR')
         NUMTODSINTERVAL (SUM (EXTRACT (MINUTE FROM PROCESS_RUN_TIME)),
                          'MINUTE')
         NUMTODSINTERVAL (SUM (EXTRACT (SECOND FROM PROCESS_RUN_TIME)),
                          'SECOND')
          AS SUM_OF_TIME
  FROM CONV_PROCESS_RUN_TIME;

And outputs:

SUM_OF_TIME
000000000 00:02:34.476507000

If you want to use your table then ignore the PROCESS_RUN_TIME column (as it is not useful as a string data-type) and use the difference between START_TIME and END_TIME:

SELECT   NUMTODSINTERVAL (SUM (EXTRACT (DAY FROM END_TIME - START_TIME)), 'DAY')
         NUMTODSINTERVAL (SUM (EXTRACT (HOUR FROM END_TIME - START_TIME)), 'HOUR')
         NUMTODSINTERVAL (SUM (EXTRACT (MINUTE FROM END_TIME - START_TIME)),
                          'MINUTE')
         NUMTODSINTERVAL (SUM (EXTRACT (SECOND FROM END_TIME - START_TIME)),
                          'SECOND')
          AS SUM_OF_TIME
  FROM CONV_PROCESS_RUN_TIME;

fiddle

CodePudding user response：

Sample data (table name is a_tbl):

START_TIME	END_TIME	RUN_TIME
13-SEP-22 06.47.42.252228000	13-SEP-22 06.48.47.077579000	000000000 00:01:02.408990
13-SEP-22 06.47.59.420078000	13-SEP-22 06.48.47.077579000	000000000 00:00:45.241140
13-SEP-22 06.48.36.964897000	13-SEP-22 06.48.47.077579000	000000000 00:00:07.696321
13-SEP-22 06.48.39.616207000	13-SEP-22 06.48.47.077579000	000000000 00:00:05.045011
13-SEP-22 06.48.42.416134000	13-SEP-22 06.48.47.077579000	000000000 00:00:02.245084
13-SEP-22 06.48.44.650970000	13-SEP-22 06.48.47.077579000	000000000 00:00:00.010248

WITH
    runtimes AS
        (
            SELECT 
            RUN_TIME,
            To_Char(To_TimeStamp(SubStr(RUN_TIME, InStr(RUN_TIME, ' ')   1), 'hh24:mi:ss:ff'), 'hh24') "RUN_HH",
            To_Char(To_TimeStamp(SubStr(RUN_TIME, InStr(RUN_TIME, ' ')   1), 'hh24:mi:ss:ff'), 'mi') "RUN_MI",
            To_Char(To_TimeStamp(SubStr(RUN_TIME, InStr(RUN_TIME, ' ')   1), 'hh24:mi:ss:ff'), 'ss') "RUN_SS",
            To_Char(To_TimeStamp(SubStr(RUN_TIME, InStr(RUN_TIME, ' ')   1), 'hh24:mi:ss:ff'), 'ff') "RUN_FF"
            From a_tbl
        )
Select
    Sum((To_Number(RUN_HH) * 3600)   (To_Number(RUN_MI) * 60)   To_Number(RUN_SS)   To_Number('0.' || RUN_FF)) "SECONDS"
From
    runtimes

Result in seconds is:

SECONDS
122.646794

Regards...

Addition:
To convert seconds to HH24:mi:ss.ff you can use modifyed Mike's answer from ( Oracle Convert Seconds to Hours:Minutes:Seconds ), I added just the fractions here:

SELECT
    TO_CHAR(TRUNC(SECONDS/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD(SECONDS,3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD(SECONDS,60)-1,'FM00') || '.' || 
    SubStr(SECONDS, InStr(To_Char(SECONDS), '.') 1) "TOTAL_RUN_TIME"
FROM
    (
        Select
            Sum((To_Number(RUN_HH) * 3600)   (To_Number(RUN_MI) * 60)   To_Number(RUN_SS)   To_Number('0.' || RUN_FF)) "SECONDS"
        From
            runtimes
    )
--  R e s u l t :
--  
--  TOTAL_RUN_TIME    
--  ------------------
--  00:02:02.646794