Oracle trunc()、interval、extract()处理日期类型

1.情景展示

在数据库中，我们经常需要对日期类型的字段进行操作，下面讲一下进行日期计算的三种方式。

2.具体分析

trunc(date[,fmt])

trunc()既可以对日期类型进行截取，也能对数值类型进行截取；

本文只介绍如何对日期进行截取，数值类型的截取方式见文末推荐。

参数说明：

　　　　　　　date 待做截取处理的日期

　　　　　　　fmt 指定要截取的日期格式。可选项，不带该参数时，截去时分秒，格式只能分为：一种 y,yy,yyy,yyyy,mm,dd,d；另一种w,iw,ww。

比较常用的是：不带第二个参数，也就是截取到日。

　　  更多用法举例：

--参数是日期，不带第2个参数
SELECT TRUNC(SYSDATE) FROM DUAL;--2017/6/7
--返回当前日期
SELECT TRUNC(SYSDATE, 'DD') FROM DUAL;--2017/6/7
--返回当年第一天
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;--2017/1/1
SELECT TRUNC(SYSDATE, 'YYY') FROM DUAL;--2017/1/1
SELECT TRUNC(SYSDATE, 'YY') FROM DUAL;--2017/1/1
SELECT TRUNC(SYSDATE, 'Y') FROM DUAL;--2017/1/1
--返回当月第一天
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;--2017/6/1
--返回当前星期的第一天
SELECT TRUNC(SYSDATE, 'D') FROM DUAL;--2017/6/4
--这种方式会报错
SELECT TRUNC(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

　　　UpdateTime--2017年7月21日11:23:20

　　　格式w,iw,ww区别介绍：

　　　参考链接：http://blog.chinaunix.net/uid-23072872-id-3664151.html

　　　这3个参数均为取每周第一天，区别如下：　　　　　　　　　　　　　

　　　　　　w，按每月的1日为每周第一天，例如1日为当月第一周第一天，8日为第二周第一天……依此类推；
　　　　　　ww，与w功能类似，不过它是按每年的1月1日为第一周第一天，1月8日为第二周第一天……依此类推；
　　　　　　iw，不受月份与年份的影响，直接取日期参数的周一。

　　　　　　举例：

--trunc(sysdate),截取当前日期年月日
--trunc(sysdate,'iw')取当前日期所在星期的星期一
--trunc(sysdate,'ww') 按年度计算取当前日期所在星期的星期一
--trunc(sysdate,'w') 按月度计算取当前日期所在星期的星期一
SELECT trunc(sysdate) 当前日期,trunc(sysdate,'iw') 取星期一,trunc(sysdate,'ww') 按年度计算取星期一,trunc(sysdate,'w') 按月度计算取星期一 FROM DUAL

extract()

extract()函数定义如下：

extract (    

        { year | month | day | hour | minute | second }    

        | { timezone_hour | timezone_minute }    

        | { timezone_region | timezone_abbr }    

from { date_value | interval_value } )

从日期类型当中提取年、月、日

select extract( year from sysdate) FROM DUAL
union
select extract( month from sysdate) FROM DUAL
union
select extract( day from sysdate) FROM DUAL

返回的是数值类型。

从日期字符串当中提取年、月、日

需要加date关键字，将日期字符串转成日期类型

select extract( year from date '2022-03-08') FROM DUAL
union
select extract( month from date '2022-03-08') FROM DUAL
union
select extract( day from date '2022-03-08') FROM DUAL

从timestamp中获取年月日时分秒

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

interval

interval '{ integer | integer time_expr | time_expr }' 

{ { day | hour | minute } [ ( leading_precision ) ] 

| second [ ( leading_precision [, fractional_seconds_precision ] ) ] } 

[ to { day | hour | minute | second [ (fractional_seconds_precision) ] } ]

leading_precision值的范围是0到9, 默认是2，表示精确到第几位；

举个栗子：

由上面，我们知道，间隔可以用：day、hour、minute等代表时间的关键词，并且最大间隔的默认最大支持2位。

那如果，我们强行使用查过2位数的间隔数呢？

意思是说：默认要求的是最多两位，你却用了3位数。

怎么办？

我们需要手动调高要支持的位数。

time_expr的格式为:hh[:mi[:ss[.n]]] or mi[:ss[.n]] or ss[.n], n表示微秒。

范围值:

hour: 0 to 23

minute: 0 to 59

second: 0 to 59.999999999

间隔1年、1月

--间隔1年
SELECT interval '1' year FROM DUAL
union
--间隔1月
SELECT interval '1' month FROM DUAL

--间隔1天
SELECT interval '1' day FROM DUAL
union
--间隔1天零1小时
SELECT interval '1 1' day to hour FROM DUAL
union
--间隔1天零1小时零1分
SELECT interval '1 1:1' day to minute FROM DUAL

--间隔1天零1小时零1分零1秒
SELECT interval '1 1:1:1' day to second FROM DUAL

 

我们可以看到：

秒后面的单位是微妙，最大支持6位数，如果超的话，会进行四舍五入。

second(2,6)表示的含义是：秒保留2位，微妙保留6位，微妙超过6位的部分按照四舍五入处理，如果不足6位的话，自动补零。

2022年4月19日19:35:15

关于trunc()的妙用

我们可以根据出生日期到系统当前时间，计算出间隔月数，并除以12取整就可以计算出年龄。

TRUNC( months_between( SYSDATE, p.BIRTH_DAY ) / 12 ) AS AGE

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• 个人主页
• oracle 常用函数
• oracle date日期类型 精析
• ORACLE判断两个日期间隔几个工作日