How to sum column which stores times in hh24:mi:ss in Oracle Sql?

Question

I have a PROCESS_TIME with data type VARCHAR2(32), in which I store the times as hh24:mi:ss, and I intend to add these times on some grouping logic.

A minified version of the table

CREATE TABLE "SCHEMA"."MY_TABLE" 
   (    "MY_TABLE_ID" VARCHAR2(32 BYTE) NOT NULL ENABLE, 
    "START_TIME" DATE NOT NULL ENABLE, 
    "END_TIME" DATE NOT NULL ENABLE, 
    "LOAD_PROCESS_TIME" VARCHAR2(32 BYTE) DEFAULT '00:00:00', 
   )

and a minified version of Insert

Insert into MY_TABLE (MY_TABLE_ID,START_TIME,END_TIME,LOAD_PROCESS_TIME) 
values ('8880508C9AC4441DB8E16E023F206C2F',to_date('05/11/2018 07:22','MM/DD/YYYY HH:MI'),to_date('05/11/2018 08:22','MM/DD/YYYY HH:MI'),'01:00:14');

Insert into MY_TABLE (MY_TABLE_ID,START_TIME,END_TIME,LOAD_PROCESS_TIME) values ('C858EB646A794D04B5C77779C50EBFCF',
to_date('05/12/2018 10:20','MM/DD/YYYY HH:MI'),
to_date('05/12/2018 11:20','MM/DD/YYYY HH:MI'),
'02:30:10');

Intended Query

SELECT TO_CHAR(TRUNC(END_TIME, 'DD'), 'DD-MON-YY'),
sum(LOAD_PROCESS_TIME)
FROM MY_TABLE 
WHERE END_TIME > SYSDATE -14 
GROUP BY TRUNC(END_TIME,'DD') 
ORDER BY TRUNC(END_TIME,'DD');

Can you please help me achieve this using Oracle SQL?

I am unable to find solution using in_built oracle functions to add such expressions, and then I finally want to group by another column. — Chaipau, May 29 '18 at 11:49
What expressions? What other columns? Please edit your question and be clear in what you want to do. Add `CREATE TABLE` statements, sample data as `INSERT INTO`, the results you expect with that sample data, the query you've already tried and what exactly is the problem with that query. — sticky bit, May 29 '18 at 11:52
Is `load_process_time` the time between `start_time` and `end_time`? — wolφi, May 29 '18 at 12:10
@Chaipau Please do not edit answers into the question. If you want to post a solution (even to your own question) you should put it in an answer. — MT0, May 29 '18 at 13:17

MT0 · Accepted Answer · 2018-05-29T12:18:36.100

You can calculate the time interval as a fractional number of days and sum that:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( PROCESS_TIME ) AS
  SELECT '01:23:04' FROM DUAL UNION ALL
  SELECT '23:00:00' FROM DUAL UNION ALL
  SELECT '11:36:56' FROM DUAL;

Query 1:

SELECT SUM(
         TO_DATE( PROCESS_TIME, 'HH24:MI:SS' )
         - TO_DATE( '00:00:00', 'HH24:MI:SS' )
       ) AS num_days
FROM   table_name

Results:

| NUM_DAYS |
|----------|
|      1.5 |

You could also create a custom object to aggregate INTERVAL DAY TO SECOND data types:

CREATE TYPE IntervalSumType AS OBJECT(
  total INTERVAL DAY(9) TO SECOND(9),

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT IntervalSumType
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT IntervalSumType,
    value       IN     INTERVAL DAY TO SECOND
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT IntervalSumType,
    returnValue    OUT INTERVAL DAY TO SECOND,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT IntervalSumType,
    ctx         IN OUT IntervalSumType
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY IntervalSumType
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT IntervalSumType
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := IntervalSumType( INTERVAL '0' DAY );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT IntervalSumType,
    value       IN     INTERVAL DAY TO SECOND
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NOT NULL THEN
      self.total := self.total + value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT IntervalSumType,
    returnValue    OUT INTERVAL DAY TO SECOND,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.total;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT IntervalSumType,
    ctx         IN OUT IntervalSumType
  ) RETURN NUMBER
  IS
  BEGIN
    self.total := self.total + ctx.total;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

And then a custom aggregation function:

CREATE FUNCTION SUM_INTERVALS( value INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE AGGREGATE USING IntervalSumType;
/

Query 2:

SELECT SUM_INTERVALS( TO_DSINTERVAL( '+0 '||PROCESS_TIME ) )
         AS total_difference
FROM   table_name

Results:

| TOTAL_DIFFERENCE |
|------------------|
|       1 12:0:0.0 |

Update - Query 3:

SELECT TO_CHAR( num_days * 24, 'FM99990' )
       || ':' || TO_CHAR( MOD( num_days * 24*60, 60 ), 'FM00' )
       || ':' || TO_CHAR( MOD( num_days * 24*60*60, 60 ), 'FM00' )
       AS total_time
FROM   (
  SELECT SUM(
           TO_DATE( PROCESS_TIME, 'HH24:MI:SS' )
           - TO_DATE( '00:00:00', 'HH24:MI:SS' )
         ) AS num_days
  FROM   table_name
)

Results:

| TOTAL_TIME |
|------------|
|   36:00:00 |

I used Query 1, how to convert the days, here 1.5 back to hh24:mi:ss, 36:00:00 — Chaipau, May 29 '18 at 12:11

score 0 · Answer 2 · answered May 30 '18 at 06:15

------------------------------answer----------------------------------------

As a completion to the discussion , the query which worked was

SELECT TO_CHAR(TRUNC(END_TIME, 'DD'), 'DD-MON-YY'),
to_char(sum(TO_DATE(LOAD_PROCESS_TIME,'HH24:MI:SS')- TO_DATE( '00:00:00', 'HH24:MI:SS' ))*24,'FM00') 
|| ':'|| TO_CHAR(MOD(sum(TO_DATE(LOAD_PROCESS_TIME,'HH24:MI:SS')- TO_DATE( '00:00:00', 'HH24:MI:SS' ))*24*60,60),'FM00')
||':'|| TO_CHAR(MOD(sum(TO_DATE(LOAD_PROCESS_TIME,'HH24:MI:SS')- TO_DATE( '00:00:00', 'HH24:MI:SS' ))*24*60*60,60),'FM00')
as Days
FROM MY_TABLE
WHERE END_TIME > SYSDATE -30 
GROUP BY TRUNC(END_TIME,'DD') 
ORDER BY TRUNC(END_TIME,'DD');

How to sum column which stores times in hh24:mi:ss in Oracle Sql?

2 Answers2