两个Date相减的结果是一个number。
两个timestamp相减的结果是一个INTERVAL值, 完整的年月日时分秒小数秒.
演示
SQL> create table test_timestamp2(time1 timestamp(9), time2 timestamp(9));
SQL> insert into test_timestamp2 values(to_timestamp('2019-02-09 11:41:10.100000','yyyy-mm-dd hh24:mi:ss.ff'),to_timestamp('2019-02-12 11:41:50.600000','yyyy-mm-dd hh24:mi:ss.ff'));
SQL> insert into test_timestamp2 values(systimestamp,systimestamp);
SQL>select to_char(time1, 'yyyy-mm-dd hh24:mi:ss.ff'), to_char(time2, 'yyyy-mm-dd hh24:mi:ss.ff')
from test_timestamp2;
SQL> select time2 - time1 from test_timestamp2;
相差3天0小时0分钟40.5秒
extrac函数获得时间的每个部分
SQL> select extract(day from time2-time1) day, extract(hour from time2 - time1) hour,
extract(minute from time2 - time1) minute,extract(second from time2 - time1) second from test_timestamp2;
从一个date类型中截取 year,month,day
date日期的格式为yyyy-mm-dd
SQL>select extract(year from date'2019-02-09') year from dual;
SQL>select extract(month from date'2019-02-09') month from dual;
SQL> select extract(day from date'2019-02-09') day from dual;
SQL> select extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(hour from systimestamp) hour
,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
备注说明
sysdate 数据库服务器操作系统时间，显示不含时区(其实隐含了时区)。
systimestamp 数据库服务器操作系统时间以及时区
这两个函数的返回值不会受到客户端影响
