而date类型相减只得到天数
SQL> SELECT sysdate-(sysdate-1) FROM dual;
SYSDATE-(SYSDATE-1)
-------------------
1
做个试验：
create table t2 (d1 date,d2 date) ;--创建一个表
INSERT INTO T2 VALUES (SYSDATE, SYSDATE + 1.1234); --插入一条数据
SQL> SELECT D1, D2, D2 - D1, CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) FROM T2;
D1 D2 D2-D1 CAST(D2ASTIMESTAMP)-CAST(D1AST
----------- ----------- ---------- ---------------------------------------
2010-12-2 1 2010-12-3 1 1.12340277 +000000001 02:57:42.000000
date类型相减转换为天、时分秒
SQL> SELECT TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) D,
2 SUBSTR(D21, INSTR(D21, ' ') + 1, 2) H,
3 SUBSTR(D21, INSTR(D21, ' ') + 4, 2) M,
4 SUBSTR(D21, INSTR(D21, ' ') + 7, 2) S
5 FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21
6 FROM T2) ;
D H M S
---------- ---- ---- ----
1 02 57 42
--两个date日期相减
select dt2 - dt1,
months_between(dt2, dt1) months_btwn,
numtodsinterval(dt2 - dt1, 'day') days,
numtoyminterval(months_between(dt2, dt1), 'month') years_months
from (select to_date('29-2-2000 01:02:03',
'dd-mm-yyyy hh24:mi:ss') dt1,
to_date('15-3-2001 11:22:33',
'dd-mm-yyyy hh24:mi:ss') dt2
from dual);
--转换为年，天时分秒
select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
numtodsinterval(dt2 -
add_months(dt1, trunc(months_between(dt2, dt1))),
'day') days_hours
from (select to_date('29-2-2000 01:02:03', 'dd-mm-yyyy hh24:mi:ss') dt1,
to_date('15-3-2001 11:22:33', 'dd-mm-yyyy hh24:mi:ss') dt2
from dual);
二、timestamp类型
timestamp 语法：timestamp（n）
--当n=0时，与date长度一样
create table t (dt date,ts timestamp(0));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t;
DUMP DUMP
------------------------------------------ ---------------------------------------
Typ=12 Len=7: 120,110,12,4,14,10,10 Typ=180 Len=7: 120,110,12,4,14,10,10
--n为9
create table t (dt date,ts timestamp(9));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t;
DUMP DUMP
----------------------------------- --------------------------------------------------------
Typ=12 Len=7: 120,110,12,4,14,7,41 Typ=180 Len=11: 120,110,12,4,14,7,41,11,37,100,192
SQL> select * from t;
DT TS
----------- -----------------------------------
2010-12-4 1 04-12月-10 01.54.09.625000000 下午 --n为9即秒后面保留的位数（625000000）
select dump(ts,16) dump from t;
Typ=180 Len=11: 78,6e,c,4,e,37,a,25,40,be,40
SQL> select to_number('2540be40','xxxxxxxx') from dual;
TO_NUMBER('2540BE40','XXXXXXXX'）--将后四个字节转换为十进制
------------------------------
625000000 --后面四个字节就是秒小数点后面的数字
--timestamp 类型包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息
SQL> SELECT sysdate,systimestamp FROM dual;
SYSDATE SYSTIMESTAMP
----------- ---------------------------------------
2011-3-4 15 04-3月 -11 03.18.16.625000 下午 +08:00
timestamp直接进行加减时会隐式的转为date型
SQL> select systimestamp ts,systimestamp+1 dt from dual;
TS