Oracle extract函数截取年月日时分秒

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 } )

1. 获取年月日

--获取年月日写法一
select sysdate, 
       extract(year from sysdate) year,
       extract(month from sysdate) month, 
       extract(day from sysdate) day
  from dual;

--获取年月日写法二, 日期之间只能用"-"
select date'2020-05-07' dateCode, 
       extract(year from date'2020-05-07') year,
       extract(month from date'2020-05-07') month, 
       extract(day from date'2020-05-07') day
  from dual;

 

--获取年月日写法三
select sysdate,
       extract(year from systimestamp) year,
       extract(month from systimestamp) month,
       extract(day from systimestamp) day
from dual;

--获取年月日写法四,日期之间只能用"-"
select timestamp'2020-05-07 12:12:12',
       extract(year from timestamp'2020-05-07 18:19:12') year,
       extract(month from timestamp'2020-05-07 18:19:12') month,
       extract(day from timestamp'2020-05-07 18:19:12') day
from dual;

 2. 获取时分秒

--获取时分秒写法一
select extract(hour from cast(systimestamp as timestamp)) hour,
       extract(minute from systimestamp) minute,
       extract(second from systimestamp) second
  from dual;

--获取时分秒写法二
select extract(hour from timestamp'2020-05-07 18:19:12') hour,
       extract(minute from timestamp'2020-05-07 18:19:12') minute,
       extract(second from timestamp'2020-05-07 18:19:12') second
 from dual;

3. 获取两个日期之间的具体时间间隔，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);