How to calculate the arithmetic mean of time oracle?

Question

I have a table in Oracle with the time value in the column, but the column type is nvarchar2, the time format is 0:21:31, how can I calculate the average value i.e. (0: 22: 00 + 0: 24: 00) = 0 : 23: 00

Answer 1

The obvious question is, why are "times" stored as character strings? This makes everything difficult. And, especially, why the N in NVARCHAR2? The strings are just digits and colon, why do you need "national character set" strings?

Be that as it may... Here is one way - which will fail in many different ways on bad inputs - where the output is again in NVARCHAR2 data type. (Notice the NCHAR, with an N, in TO_NCHAR(), which I have never seen anyone use.) The inputs are given as columns A and B in a made-up table T in the WITH clause (which is there just for testing, it is not part of the solution; use your actual table and column names, and remove the WITH clause at the top).

with t as (select '0:24:00' a, '0:22:00' b from dual)
select to_nchar(date '2000-01-01'
                + ( (to_date(a, 'hh24:mi:ss') - date '2000-01-01')
                  + (to_date(b, 'hh24:mi:ss') - date '2000-01-01')
                  ) / 2, 'hh24:mi:ss') as avg_ab
from   t
;

AVG_AB          
----------------
00:23:00

If instead all your times are in a single column, call it A, you could use standard AVG, but still needing to play with TO_DATE() and TO_NCHAR()...

with t as (select '0:24:00' a from dual union all select '0:20:30' from dual)
select to_nchar(date '2000-01-01' 
                + avg(to_date(a, 'hh24:mi:ss') - date '2000-01-01'),
                'hh24:mi:ss') as avg_a
from   t
;

AVG_A           
----------------
00:22:15