- //语法如下:
- EXTRACT
( -
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } -
| { TIMEZONE_HOUR | TIMEZONE_MINUTE } -
| { TIMEZONE_REGION | TIMEZONE_ABBR } - FROM
{ date_value | interval_value } ) - //我们只可以从一个date类型中截取
year,month,day(date日期的格式为yyyy-mm-dd); - //我们只可以从一个
timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE; - select
extract(year from date'2011-05-17') year from dual; -
YEAR - ----------
-
2011 - select
extract(month from date'2011-05-17') month from dual; -
MONTH - ----------
-
5 - select
extract(day from date'2011-05-17') day from dual; -
DAY - ----------
-
17 - //获取两个日期之间的具体时间间隔,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) - /
-
DAY HOUR MINUTE SECOND - ----------
---------- ---------- ---------- -
102 4 1 46 - --
- select
extract(year from systimestamp) year -
,extract(month from systimestamp) month -
,extract(day from systimestamp) day -
,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 - /
-
YEAR MONTH DAY MINUTE SECOND TH TM TR TA - ----------
---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- -
2011 5 17 7 14.843 8 0 UNKNOWN UNK - //
Oracle EXTRACT()函数
最新推荐文章于 2024-04-26 00:08:56 发布