sql - Convert VARCHAR2 into Number

Question

I have a table with a column named duration. Its data type is VARCHAR2.

I want to sum the column duration.

00:56:30
02:08:40
01:01:00

Total=> 04:05:10

How can I do this using ANSI SQL or Oracle SQL?

Answer 1

You can separate the hours, mins and seconds using SUBSTR, then SUM it up and finally use NUMTODSINTERVAL function to convert it into INTERVAL type.

SELECT NUMTODSINTERVAL (SUM (total_secs), 'second')
  FROM (SELECT   SUBSTR (duration, 1, 2) * 3600
               + SUBSTR (duration, 4, 2) * 60
               + SUBSTR (duration, 7, 2) total_secs
          FROM user_tab);