Oracle时间处理函数大全(附超全面案例)

Oracle常用时间函数

add_months(date,int):输入date数据和需要增加的月份数(整数),返回增加若干月份数后date数据。

 -- 加一个月后的日期
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual;
-- 扩展
-- 求当年的天数:增加12月后再取年份相减
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') days FROM dual;

last_day(date):返回当前日期在该月份的最后一天的日期

SELECT LAST_DAY(SYSDATE) from dual;
-- 假设今天为2021-04-21,那么执行返回的时间日期为2021-04-30

months_between(date1,date2):返回两个日期的月份差,存在小数。

-- 返回值刚好为1,因为都是两个月份的02相减。
-- 提示:如果不相等则有很多小数。这时候使用floor进行向下取整或round进行向上取整
SELECT MONTHS_BETWEEN(to_date('2021-05-02','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd')) as month from dual;

项目实际案例

加减时间(年月日时分秒)
-- 当前时间减去10分钟,同理换成year(年)、month(月)、day(日)、hour(时)、second(秒)
select sysdate,sysdate - interval '10' minute from dual;
-- 也可以换成加10分钟。
select sysdate,sysdate + interval '10' minute from dual;
求时间为星期几或者几月份或多少年
select to_char(sysdate,'D') from dual; --这周的第几天;每周的第一天为星期天,星期一为第二天
--某天星期几 同样可以求多少年(year)多少月(month)
select to_char(to_date('2021-04-08','yyyy-mm-dd'),'day') from dual; --20210408为星期四
--输出英文。某天星期几 同样可以求多少年多少月
select to_char(to_date('2021-04-08','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = AMERICAN') from dual; 
求两个日期之间的月份
-- 求两个日期之间的月份(如果日期存在差值,则会有很长的小数,使用floor函数可以向下取整。)
-- 结果0,不满一个月
SELECT floor(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd'))) as month from dual;  
-- 求两个日期之间的月份(如果日期存在差值,则会有很长的小数,使用round函数可以四舍五入。)
-- 结果1,过了一半就算一个月。根据需求选择
SELECT ROUND(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd')),0) as month from dual;

-- 扩展
-- 求人员的年龄
-- 月份相减计算年龄(推荐)
SELECT TRUNC(MONTHS_BETWEEN(to_date('2021-04-01','yyyy-mm-dd'), to_date('1997-04-02','yyyy-mm-dd'))/12,0) as age from dual;  

有关Oracle数字型数据的处理相关函数,请查看这篇文章:Oracle处理数字型数据。比如说floor、trunc都有介绍。

求两个日期的天数
--两个日期间的天数差,同理除365获得年,除30得到月。乘于24得到小时差
select floor(TO_NUMBER(sysdate - to_date('20200405','yyyymmdd'))) from dual;
计算年龄
 -- 月份相减计算年龄(推荐)
SELECT TRUNC(MONTHS_BETWEEN(to_date('2021-04-01','yyyy-mm-dd'), to_date('1997-04-02','yyyy-mm-dd'))/12,0) as age from dual; 
--年份相减计算年龄
SELECT TO_CHAR(to_date('2021-04-01','yyyy-mm-dd'),'YYYY') - TO_CHAR(to_date('1997-04-02','yyyy-mm-dd'),'YYYY') as age from dual; 
计算分钟差
-- 求两个时间的分钟差 什么都不乘代表天数,不能四舍五入
SELECT floor(TO_NUMBER(SYSDATE - TO_DATE('2021-04-15 14:05:20', 'YYYY-MM-DD hh24:mi:ss')) * 24 *60) as time from dual;
计算一个月中,星期一至星期五的天数
 -- 求一个月中除开星期六和星期天的天数
 -- 思路:获取累加数,通过日期累加rownum在tochar取星期几判断即可
 -- 注意:星期日为一周的开始即是1,星期一为2以此类推
select count(*)     
   from (select rownum-1 rnum from all_objects where rownum <= to_date('2002-04-30','yyyy-mm-dd') - to_date('2002-04-01','yyyy-mm-dd')+1)
   where to_char(to_date('2021-04-01','yyyy-mm-dd')+rnum,'D') not in ('1','7');
求今天是这个星期的第几天,这个月第几天,今年第几天
select to_char(sysdate,'D') from dual; --这个星期的第几天

select to_char(sysdate,'DD') from dual; --这个月的第几天

select to_char(sysdate,'DDD') from dual; --今年的第几天
求不同时间格式的第一天
-- 通过trunc函数截取标准不同,获取不同时间的开始
select TRUNC(sysdate,'DD') from dual;-- 今天的开始

select TRUNC(sysdate,'iw') from dual;-- 这周的第一天

select TRUNC(sysdate,'MM') from dual;-- 这个月的第一天

select TRUNC(sysdate,'q') from dual;-- 这个季度的第一天

select TRUNC(sysdate,'YYYY') from dual; --今年第一天
时间格式化
-- 时间格式化为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; 
-- 返回的是自然周于本年的周数,一年周数最少不低于52  
select to_char(SYSDATE,'iw') from dual;
--返回季度
select to_char(sysdate,'q') from dual; 
--当前时间的年 
select to_char(sysdate,'yyyy') from dual; 
--当前时间的月   
select to_char(sysdate,'mm') from dual;  
--当前时间的日   
select to_char(sysdate,'dd') from dual;  
--当前时间的时  
select to_char(sysdate,'hh24') from dual;
--当前时间的分     
select to_char(sysdate,'mi') from dual; 
--当前时间的秒    
select to_char(sysdate,'ss') from dual;   
求两个时间的天数、小时、分钟和秒数差
-- 求两个时间的天数、小时差、分钟差和秒数差
SELECT 
	time,
	floor(time) day,
	case when time < 1 then TRUNC(time*24) when time > 1 then MOD(TRUNC(time*24), 24) else 0 END as hour,
	TRUNC(time*24*60 - 60*TRUNC(time*24)) minute, -- 取整再乘60分再相减就可以得出小数部分的分钟数
	TRUNC(time*24*60*60 - 60*TRUNC(time*24*60)) second
FROM 
	(SELECT TO_NUMBER(SYSDATE - TO_DATE('2021-04-15 14:05:20', 'YYYY-MM-DD hh24:mi:ss')) as time from dual) 
求当月天数
-- extract(day from date) day为时间关键字,date为日期
-- 使用extract函数获取当月第一天到date的天数
SELECT extract(day from last_day(sysdate)) FROM dual;
  • 6
    点赞
  • 37
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle中,函数索引(Function-based Index)可以用来优化查询中的函数操作。但是,如果函数操作的参数值分布不均匀,就会导致数据倾斜(Data Skew)问题,从而使查询性能下降。 下面以一个简单的案例来说明如何利用函数索引处理数据倾斜问题。 假设有一个表T,包含两个字段:ID(主键)和VALUE。现在需要根据VALUE字段进行查询,其中VALUE字段的取值较为分散,但是查询时会经常使用到一个函数F,它可以将VALUE转换为一个数字。 为了优化查询性能,可以创建一个基于函数F的函数索引,示例如下: ``` CREATE INDEX idx_value ON T(F(VALUE)); ``` 这样,在查询时就可以利用函数索引来加速查询,例如: ``` SELECT * FROM T WHERE F(VALUE) = 100; ``` 但是,如果数据倾斜比较严重,例如,大部分数据的F(VALUE)值都比较小,只有极少数数据的F(VALUE)值比较大,那么查询时就会出现性能问题。 为了解决这个问题,可以考虑使用多个函数索引来分散数据。例如,可以按照VALUE字段的范围来创建多个函数索引,示例如下: ``` CREATE INDEX idx_value_1 ON T(F(VALUE)) WHERE VALUE BETWEEN 0 AND 99; CREATE INDEX idx_value_2 ON T(F(VALUE)) WHERE VALUE BETWEEN 100 AND 199; CREATE INDEX idx_value_3 ON T(F(VALUE)) WHERE VALUE BETWEEN 200 AND 299; ... ``` 这样,在查询时就可以根据具体的查询条件来选择合适的函数索引,从而避免数据倾斜问题。 需要注意的是,创建多个函数索引会增加存储空间和维护成本,因此需要根据实际情况来进行权衡。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值