Oracle data type INTERVAL

Literals

select
   --          interval  literals            
   -------------------------------------------------
   interval  '17-9'             year(3) to month       as seventeen_years_and_nine_months,
   interval '-42-9'             year    to month       as minus_forty_two_years_and_9_months,
   interval '1004 14:15:16'     day (4) to second      as approx_1000_days_14_hours_15_minutes_and_16_seconds,
   interval    '2 14:15:16.61'  day (4) to second(1)   as specify_fractions
from
   dual;

Get duration of interval in seconds

select
    extract(day    from interval    '0 01:01:01' day(4) to second * 86400) s_1,   -- one hour, one minute and one second
    extract(day    from interval '1234 05:06:07' day(4) to second * 86400) s_2,   -- = 1234*24*60*60 + 5*60*60 + 6*60 + 7
    extract(second from interval    '0 01:01:01' day(4) to second        ) wrong  -- = 1 (which is not what was intended)
from
   dual;

AVG and SUM

create table tq84_avg_interval (
    crt   varchar2(10),
    tim  interval day(3) to second(0)
);

begin
   insert into tq84_avg_interval values ('abc', '1 01:01:01');
   insert into tq84_avg_interval values ('abc', '3 03:03:03');
   insert into tq84_avg_interval values ('xyz', '2 02:02:02');
   commit;
end;
/

select
   crt,
   avg(tim) avg_,
   sum(tim) sum_
from
   tq84_avg_interval
group by
   crt;

select
   crt,
   interval '0 00:00:01' day(3) to second(0) * avg(extract (day from tim * 86400)) avg_,
   interval '0 00:00:01' day(3) to second(0) * sum(extract (day from tim * 86400)) sum_
from
   tq84_avg_interval
group by
   crt;

drop table tq84_avg_interval purge;

Some statements

create table tq84_intervals (
  ym  interval year to month,
  ds  interval day  to second(0)
);


insert into tq84_intervals values (
  numtoyminterval(
    months_between(date '2018-05-02',
                   date '2016-03-05'),
    'month'   
  ), 
  numtodsinterval(
      trunc(sysdate) + 4 + 7/24 + 26/24/60 + 19/24/26/60 
    - trunc(sysdate),
    'day'
  )
);


insert into tq84_intervals values (
  numtoyminterval(
    months_between(date '2015-10-10',
                   date '2017-10-10'),
    'month'   
  ), 
  numtodsinterval(1 + 2/24 + 3/24/60 + 4/24/26/60, 'day'
  )
);


select * from tq84_intervals
  where
    ds < numtodsinterval(2, 'day');

drop table tq84_intervals purge;

See also