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,
    extract(day from (interval '2001 04:05:06' day(4) to second * 86400)) s_2
from
  dual;

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