ORACLE中的时间函数TRUNC与TO_DATE

ORACLE中的时间函数TRUNC与TO_DATE

TRUNC的常用用法

我们知道在oracle中，trunc经常用来做时间与取整计算，本文主要演示一下trunc在时间操作函数中的作用
1.oracle常用时间类型查询
时间字段为yyyy-MM-dd格式时，可以不使用trunc，后续时间使用to_date等
–当前时间带时分秒 2020/4/11 22:09:56
select sysdate from dual;
–当前时间 截取到天 2020/4/11
select trunc(sysdate) from dual;
–当前时间后一天/前一天 2020/4/12 2020/4/10 不取整去掉trunc
select trunc(sysdate-1/-1/) from dual;
–当月 2020/4/1
select trunc(sysdate,‘mm’) from dual;
–当月最后一天 2020/4/30
select trunc(last_day(sysdate/date’2020-02-01’/)) from dual;
–上月同一天/下月同一天 2020/3/11 2020/5/11
select add_months(trunc(sysdate),1) from dual;
–当年 2020/1/1
select trunc(sysdate,‘yyyy’) from dual;
–常用查询
–某天数据
select * from tablseName p where trunc(p.时间字段)=date’2020-01-01’;
–今天数据
select * from tablseName p where trunc(p.时间字段)=trunc(sysdate);
–某月数据
select * from tablseName p where trunc(p.时间字段,‘mm’)=date’2020-02-01’;
–某年数据
select * from tablseNamep where trunc(p.时间字段,‘yyyy’)=date’2020-01-01’;
–时间区间
select * from tablseNamep where trunc(p.时间字段) between date’2020-01-01’ and date’2020-01-02’;
–时间时分秒
select * from tablseNamep where p.时间字段between to_date(‘2020-01-01 11:00:00’,‘yyyy-mm-dd hh24:mi:ss’) and date’2020-01-02’;
–减5小时
select sysdate,to_char(sysdate-5/24,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–减5分钟
select sysdate,to_char(sysdate-5/24/60,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–减5秒
select sysdate,to_char(sysdate-5/24/60/60,‘yyyy-mm-dd HH24:MI:SS’) from dual;
2.关于trunc与to_date的比较----个人分析意见。
在实际使用中，推荐使用to_date作为时间条件，在之前看到的文章中，分析trunc需要的cpu时间大于了使用to_date函数。在实际的生产环境中，通过oracle的执行计划发现，to_date的字节量是大于trunc函数的，但是在执行过程中，to_date函数性能是高于trunc的，我测试的数据量是500w左右数据。分析原因：
1.to_date是将字符串转变为时间类型后与字段逐个比较，使用的是单次函数转换。
2.trunc函数是对oracle行取到的数据进行函数操作。
3.关于时间函数用作数据恢复的临时用法
不讲解原理只给出SQL，可以用作oracle短时间内的恢复快照。
–查询表建立时间，最后更新时间 2018/2/2 11:01:50 2020/2/25 11:02:26
SELECT CREATED,LAST_DDL_TIME from user_objects where object_name=upper(‘tableName’);
–查询指定时间戳数据 可以用作恢复数据，只能恢复短时间戳内，时间过久此方法不适用
select * from tableName as of timestamp to_Date(‘2020-04-11 19:00:00’,‘yyyy-mm-dd hh24:mi:ss’);
–所有用户表查询
select * from all_tab_comments;
–查询当前用户
select user from dual;