Oracle入门精读80-Oracle时间间隔interval的操作（函数extract）

两个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 数据库服务器操作系统时间以及时区

这两个函数的返回值不会受到客户端影响