1  select
  2  EXTRACT(YEAR FROM
  3  (
  4   (to_date('2008-07-15 15:23:02','yyyy-mm-dd hh24:mi:ss')
  5  -to_date('2005-08-15 15:22:01','yyyy-mm-dd hh24:mi:ss'))
  6* day to second)) "interval (s)" from dual
SQL> /
-to_date('2005-08-15 15:22:01','yyyy-mm-dd hh24:mi:ss'))
*
ERROR at line 5:
ORA-30076: неверное поле выборки для источника выборки

SQL> SELECT  ((TRUNC(SYSDATE) + day_to_second_interval) - TRUNC(SYSDATE)) * 86400 sec
  2    FROM  (
  3           SELECT  numtodsinterval((to_date('2008-07-15 15:22:01')-to_date('2005-06-15 15:22:01')),'DAY') day_to_second_interval
  4             FROM  DUAL
  5          )
  6  /

       SEC
----------
  97286400

SQL> 

CREATE OR REPLACE FUNCTION unixtime(x DATE) 
return number as
begin
return (x - to_date('01011970','ddmmyyyy'))*24*60*60
-extract(TIMEZONE_HOUR from FROM_TZ(cast(x as timestamp),'Europe/Moscow'))*3600;
end;

select unixtime(to_date('2008-07-15 15:22:01','YYYY-MM-DD HH24:MI:SS')) 
-unixtime(to_date('2005-06-15 15:22:01','YYYY-MM-DD HH24:MI:SS')) from dual;

select extract(month from ((sysdate+15-sysdate) year to month)) "month" from dual;

select extract(month from (((sysdate+10)+15-(sysdate+10)) year to month)) "month" from dual;

select cast((sysdate+1-sysdate) as interval year to month) from dual;

select to_date('30-01-2008','dd-mm-yyyy')+interval '00-01' year to month from dual;

select to_date(1e7-1, 'j') from dual;

cast((sysdate+10-sysdate)*86400*interval '1'second  as INTERVAL DAY TO SECOND)

numtodsinterval(sysdate+10-sysdate, 'day')