1.只是粗略返回相隔的天数
我们知道在Oracle中两个日期相减返回的就是相差的天数，当然这个天数未必是整数，它会把两个日期相差的时分秒折算成天数 SQL> SELECT SYSDATE-HIREDATE FROM EMP;
SYSDATE-HIREDATE
----------------
3.12123843
9316.66291
9314.66291
SQL> SELECT SYSDATE-HIREDATE,TRUNC(SYSDATE-HIREDATE) FROM EMP;
SYSDATE-HIREDATE TRUNC(SYSDATE-HIREDATE)
---------------- -----------------------
3.12486111 3
9316.66653 9316
9314.66653 9314
2.返回精确的时间间隔
比如我要得到两个日期相隔几天几小时几分几秒，这时就不能用TRUNC来实现了，我们可以用NUMTODSINTERVAL和EXTRACT来实现：
SQL> SELECT EXTRACT(DAY FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) DAY,
2 EXTRACT(HOUR FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) HOUR,
3 EXTRACT(MINUTE FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) MINUTE,
4 NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY') DETAIL
5 FROM EMP;
DAY HOUR MINUTE DETAIL
---------- ---------- ---------- ------------------------------
3 3 31 +000000003 03:31:54.999999999
9316 16 31 +000009316 16:31:55.000000000
9314 16 31 +000009314 16:31:55.000000000
Oracle计算时间函数(numtodsinterval、numtoyminterval)
numtodsinterval(<x>,<c>),x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 ('day','hour','minute','second')
example
SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:45:34 2007-09-05 04:45:34
numtoyminterval与numtodsinterval函数类似,将x转为interval year to month数据类型
常用的单位有'year','month'
example
SQL> select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:54:53 2010-09-05 01:54:53
//Oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分
//语法如下：
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);
//我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
select extract(year from date'2011-05-17') year from dual;
YEAR
----------
2011
select extract(month from date'2011-05-17') month from dual;
MONTH
----------
5
select extract(day from date'2011-05-17') day from dual;
DAY
----------
17
//获取两个日期之间的具体时间间隔，extract函数是最好的选择
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from (
select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1
,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
from dual)
/
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
102 4 1 46
--
select extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual
/
YEAR MONTH DAY MINUTE SECOND TH TM TR TA
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------
2011 5 17 7 14.843 8 0 UNKNOWN UNK
