extract函数浅析

--extract函数
EXTRACT( { YEAR
         | MONTH
         | DAY
         | HOUR
         | MINUTE
         | SECOND
         | TIMEZONE_HOUR
         | TIMEZONE_MINUTE
         | TIMEZONE_REGION
         | TIMEZONE_ABBR
         }
         FROM { expr }
       )
	   
EODA@PROD1> select dt, extract(year from dt) year, extract(month from dt) month from (select to_date('30-jan-2000','dd-mon-yyyy hh24:mi:ss') dt from dual );

DT			   YEAR      MONTH
-------------------- ---------- ----------
30-jan-2000 00:00:00	   2000 	 1

--往往被用来获取两个日期之间的具体时间间隔
EODA@PROD1> drop table t purge;

Table dropped.

EODA@PROD1> create table t (dt1 timestamp, dt2 timestamp);

Table created.

EODA@PROD1> insert into t values(to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss'),to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss'));

1 row created.

EODA@PROD1> select dt1,dt2 from t;

DT1
---------------------------------------------------------------------------
DT2
---------------------------------------------------------------------------
04-FEB-11 03.07.00.000000 PM
17-MAY-11 07.08.46.000000 PM


EODA@PROD1> 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 t;

       DAY	 HOUR	  MINUTE     SECOND
---------- ---------- ---------- ----------
       102	    4	       1	 46