Obtener tiempo entre fechas

[YaninaGenia]

A ver si nos entendemos,
mi pregunta fue:

¿Hay alguna forma de que los resultados me los devuelva en el formato hh:mi:ss? Por ejemplo, entre las fechas
12/04/2006 14:06:53 y 12/04/2006 16:41:32 que me devuelva 2:34:39.

Si me devuelve en decimal, por ejemplo 1,5476, ¿como hago para pasarla al formato hh:mi:ss?

[delphi.com.ar]

Por aquí encontre esta posible solución:

Código SQL [-]
create or replace function time_between (start_tm in date, end_tm in date,
   hours_only varchar2 default 'N') return varchar2 as
-- If "hours_only" is null or "N", the return will be a string formatted like:
--     2 days, 3 hrs, 5 mins, 10 secs
-- If "hours_only" is not "N", then the return is a value in hours, like 102.325
  ret_val   varchar2(80);
  start_sec number;
  end_sec   number;
  full_sec  number;
  balance   number;
  minute    number;
  hour      number;
  days      number;
--
  function get_sec (time_in in date) return number as
  begin
    return to_number(to_char(time_in,'SSSSS')); 
  end;
--
begin
  start_sec := get_sec(start_tm);
  end_sec := get_sec(end_tm);
  -- check if end time is in the same day as start time
  if to_char(start_tm,'YYMMDD') = to_char(end_tm,'YYMMDD') then
    full_sec := end_sec - start_sec;
    days := 0;
  else
    days := trunc(end_tm - start_tm);
    if days > 0 then
      ret_val := to_char(days)||' days, ';
    end if;
    if end_sec > start_sec then
      full_sec := end_sec - start_sec;
    else
      full_sec := 86400 - start_sec + end_sec;
    end if;
  end if;
  if upper(hours_only) = 'N' then
    if full_sec > 3599 then
      hour := floor(full_sec / 3600);
      balance := mod(full_sec,3600);
      full_sec := balance;
      if hour > 1 then
        ret_val := ret_val||to_char(hour)||' hrs, ';
      else
        ret_val := ret_val||to_char(hour)||' hr, ';
      end if;    
    end if;
    if full_sec > 59 then
      minute := floor(full_sec / 60);
      balance := mod(full_sec,60);
      full_sec := balance;
      if minute > 1 then
        ret_val := ret_val||to_char(minute)||' mins, ';
      else
        ret_val := ret_val||to_char(minute)||' min, ';
      end if;    
    end if;
    ret_val := ret_val||to_char(full_sec)||' secs';
  else
    -- Calculate the time difference in hours, to three decimal places
    ret_val := to_char((24 * days) + round((full_sec / 3600),3));
  end if;
  return ret_val;
end;
/
grant execute on time_between to public;
create public synonym time_between for time_between;

Saludos!