CODEWITHSUNDEEP
oracle-database
Outside_the_box
Outside_the_box

Reputation: 5

Convert time from a format to an int in Oracle

I can't seem to figure this out. I have some rows with time in the format 00:00:00 (hh:mm:ss) and i need to calculate the total time it takes for a task. I am unable to sum this data. Can someone advise on a way to convert this to a format i can sum or a method to calculate the total time for the task. Thanks for any assistance. This is in an Oracle DB.

Upvotes: 0

Views: 158

Answers (1)

MT0
MT0

Reputation: 167774

Convert your time string to a date and subtract the equivalent date at midnight to give you an number as a fraction of a day. You can then sum this number and convert it to an interval:

Oracle Setup:

CREATE TABLE test_data( value ) AS
  SELECT '01:23:45' FROM DUAL UNION ALL
  SELECT '12:34:56' FROM DUAL UNION ALL
  SELECT '23:45:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
         SUM( TO_DATE( value, 'HH24:MI:SS' ) - TO_DATE( '00:00:00', 'HH24:MI:SS' ) ),
         'DAY'
       ) AS total_time_taken
FROM   test_data;

Output:

| TOTAL_TIME_TAKEN              |
| :---------------------------- |
| +000000001 13:43:41.000000000 |

db<>fiddle here

Update including durations longer than 23:59:59.

Oracle Setup:

CREATE TABLE test_data( value ) AS
  SELECT '1:23:45' FROM DUAL UNION ALL
  SELECT '12:34:56' FROM DUAL UNION ALL
  SELECT '23:45:00' FROM DUAL UNION ALL
  SELECT '48:00:00' FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
         SUM(
           DATE '1970-01-01'
           + NUMTODSINTERVAL( SUBSTR( value, 1, HM - 1 ), 'HOUR' )
           + NUMTODSINTERVAL( SUBSTR( value, HM + 1, MS - HM - 1 ), 'MINUTE' )
           + NUMTODSINTERVAL( SUBSTR( value, MS + 1 ), 'SECOND' )
           - DATE '1970-01-01'
         ),
         'DAY'
       ) AS total_time
FROM   (
  SELECT value,
         INSTR( value, ':', 1, 1 ) AS HM,
         INSTR( value, ':', 1, 2 ) AS MS
  FROM   test_data
);

Output:

| TOTAL_TIME                    |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Even better would be if you changed your table to hold the durations as intervals rather than as strings then everything becomes much simpler:

Oracle Setup:

CREATE TABLE test_data( value ) AS
  SELECT INTERVAL  '1:23:45' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT INTERVAL '23:45:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT INTERVAL '48:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT NUMTODSINTERVAL(
         SUM( DATE '1970-01-01' + value - DATE '1970-01-01' ),
         'DAY'
       ) AS total_time
FROM   test_data;

Output:

| TOTAL_TIME                    |
| :---------------------------- |
| +000000003 13:43:41.000000000 |

db<>fiddle here

Upvotes: 1

Related Questions