oracle 用interval 求时间 .

--指定的时间加上一分钟

SELECT to_char(to_date('2011-11-09 15:30:30','YYYY-MM-DD HH24:MI:SS') + (1/1440),'YYYY-MM-DD HH24:MI:SS')  as newDate FROM dual;

 

 

--两个时间相减

select to_date('2011-11-09 15:30:30', 'YYYY-MM-DD HH24:MI:SS') - to_date('2011-10-09 15:30:30', 'YYYY/MM/DD HH24:MI:SS') from dual

 

 

--减去10分钟

select sysdate,sysdate - interval '10' minute  as "当前时间-10分钟" from dual;

--结果：11-9-2011 5:09:34 PM ,11-9-2011 4:59:34 PM

 

 

--减去10小时

select sysdate,sysdate - interval '10' hour  as "当前时间-10分钟" from dual;

--结果：11-9-2011 5:10:59 PM ,11-9-2011 7:10:59 AM

 

 

--减去10天

select sysdate,sysdate - interval '10' day  as "当前时间-10分钟" from dual;

--结果：11-9-2011 5:11:42 PM, 10-30-2011 5:11:42 PM

select sysdate - interval '10' day as "10天前",
       sysdate - interval '10' hour as "10小时前",
       sysdate - interval '10' minute as "10分钟前",
       sysdate - interval '10' second as "10秒钟前",
       sysdate - 10 as "10天前",
       sysdate - 10 / 24 as "10小时前",
       sysdate - 10 / (24 * 60) as "10分钟前",
       sysdate - 10 / (24 * 3600) as "10秒钟前"
  from dual;

这里的 interval表示某段时间,格式是: interval '时间' <day/hour/minute/second> ;

例如 interval '10' day 表示10天