oracle中对时间和时间戳的一些常规用法

秒速五厘米 2022-04-14 05:10 565阅读 0赞

## 1、加减日月年 ##

在oracle中date类型可以直接加减天数，月份要用 add\_months 函数

SELECT
        SYSDATE 今天,
        SYSDATE - 5 减5天,
        SYSDATE + 5 加5天,
        add_months(SYSDATE,+5) 加5个月,
        add_months(SYSDATE,-5) 减5个月,
        add_months(SYSDATE,+5*12) 加年,
        add_months(SYSDATE,-5*12) 减5年
    FROM
        dual

![20181124183616895.png][]

## 2、加减时分秒 ##

先临时修改时间格式：

alter session set nls_date_format='yyyy-mm-DD HH24:MI:SS'

SELECT
        SYSDATE 今天,
        SYSDATE - 5/24/60/60 减5秒,
        SYSDATE + 5/24/60/60 加5秒,
        SYSDATE - 5/24/60    减5分,
        SYSDATE + 5/24/60    加5分,
        SYSDATE - 5/24       减5小时,
        SYSDATE + 5/24       加5小时
    FROM
        dual;

![2018112419040144.png][]

## 3、日期间隔时分秒 ##

两个date类型相减就是天数，乘以24就是小时，以此类推就可以算出秒

SELECT
        间隔天数,
        间隔天数 * 24 AS 间隔小时,
        间隔天数 * 24 * 60 AS 间隔分,
        间隔天数 * 24 * 60 * 60 AS 间隔秒
    FROM
        (
            SELECT
                SYSDATE - start_date AS 间隔天数
            FROM
                td_notice
            WHERE
                ROWNUM <= 1
        )

![20181124191800186.png][]

##  4、日期间隔日月年 ##

SELECT
        one-two  间隔天,
        months_between(one,two) 间隔月,
        months_between(one,two)/12 间隔年
    FROM
        (
            SELECT
                SYSDATE as one,
                SYSDATE-400 as two
            FROM
               dual
        )

![20181124192628930.png][]

##  5、其他操作 ##

获得当月月初

SELECT
        SYSDATE 当前日期,
        TO_DATE(
            TO_CHAR(SYSDATE,'yyyy-mm')
             || '-1',
            'yyyy-mm-dd'
        ) 月初
    FROM
        dual

![20181124212459881.png][]

SELECT
        SYSDATE 当前日期,
        trunc(SYSDATE,'mm') 月初
    FROM
        dual

![20181124212659710.png][]

select  sysdate,
            to_number(to_char(sysdate,'hh24')) 时,
            to_number(to_char(sysdate,'mi')) 分,
            to_number(to_char(sysdate,'ss')) 秒,
            to_number(to_char(sysdate,'dd')) 日,
            to_number(to_char(sysdate,'mm')) 月,
            to_number(to_char(sysdate,'yyyy')) 年,
            to_number(to_char(sysdate,'ddd')) 年内第几天,
            trunc(sysdate,'dd') 一天之始,
            trunc(sysdate,'day') 周初,
            trunc(sysdate,'mm') 月初,
            last_day(sysdate) 月末，
            trunc(sysdate,'yy') 年初,
            to_char(sysdate,'day') 周几,
            to_char(sysdate,'month') 月份 
    from dual

![20181124215813551.png][]

## 6 时间戳的操作 ##

> ** 别名不能用单引号，可以用双引号或不用引号**

--别名不能用单引号，可以用双引号或不用引号
    SELECT
        systimestamp,
        EXTRACT(YEAR FROM systimestamp) AS "year",
        EXTRACT(MONTH FROM systimestamp) AS "month",
        EXTRACT(DAY FROM systimestamp) AS "day",
        EXTRACT(HOUR FROM systimestamp) AS "hour",
        EXTRACT(MINUTE FROM systimestamp) AS "minute",
        EXTRACT(SECOND FROM systimestamp) AS "second"
    FROM
        dual

![20181125235202973.png][]

extract 不能去date类型中的时分秒

## 7  确定一年是否为闰年 ##

主要是看二月月末是哪一天

select trunc(sysdate,'y') 年初 from dual;

select add_months(trunc(sysdate,'y'),1) 二月初 from dual;

select last_day(add_months(trunc(sysdate,'y'),1)) 二月底 from dual;

select to_char(last_day(add_months(trunc(sysdate,'y'),1)),'DD') 日 from dual;

![20181125235951175.png][]

## 8、创建本月日历 ##

枚举指定月份的所有日期，并转化为对应的周信息，再按所在周做一次“”行转列”” 即可

--1给定一个日期
    with x1 as (select to_date('2018-11-11','yyyy-mm-dd') as cur_date from dual), 
    --2取月初
         x2 as  (select trunc(cur_date,'mm') as 月初,add_months(trunc(cur_date,'mm'),1) as 下月初 from  x1),
    --3枚举当月所有天
         x3 as  (select 月初 + (level - 1) as 日 from x2 connect by level <=(下月初-月初)),
    --4提取周信息
         x4 as  (select to_char(日，'iw') 所在周,
                        to_char(日,'dd')  日期,
                        to_number(to_char(日,'d')) 周几
                        from x3)
                        
    select  max(case 周几 when 2 then 日期 end) 周一,                   
            max(case 周几 when 3 then 日期 end) 周二,   
            max(case 周几 when 4 then 日期 end) 周三,
            max(case 周几 when 5 then 日期 end) 周四,
            max(case 周几 when 6 then 日期 end) 周五,
            max(case 周几 when 7 then 日期 end) 周六,   
            max(case 周几 when 1 then 日期 end) 周日
        from x4 group by 所在周 order by 所在周;

![20181126002205818.png][]

## 9、时间的比较 ##

**date 类型可以直接用> 、=或<比较  
也可以用between d1 and d2  (类似于>=d1 and <=d2，其中d1、d2为date类型)     **

**在oracle中时间字符串格式也可以比较** ，这是我无意间发现的，感觉很神奇

select id,CREATE_DATE from student where to_char(CREATE_DATE,'yyyy-mm-dd') >= '2018-11-15'

把时间转换成字符串直接可以和时间字符串比较

select id,CREATE_DATE from student where '2018-11-16' >= '2018-11-15' 		
    select id,CREATE_DATE from student where '2018-11-16' <= '2018-11-15'

上面这两条sql

第一条以为where后面时间字符串比较返回true，因此查询出所有数据，第二条则相反什么也没返回

[20181124183616895.png]: /images/20220414/07c13432cfbb4301a22c0a1b4836b4df.png
[2018112419040144.png]: /images/20220414/b6d1cb7236af425086cac5022db02c13.png
[20181124191800186.png]: /images/20220414/ef21528807aa48078a3e70cd16a19ab2.png
[20181124192628930.png]: /images/20220414/53a5fca3a79f4db8abab8986d91af972.png
[20181124212459881.png]: /images/20220414/b98e3131dde048259e14e25e808e8d60.png
[20181124212659710.png]: /images/20220414/556642b758984fa1a63346ade5d1d262.png
[20181124215813551.png]: /images/20220414/a80c935690d548c3b7040dee36acc1da.png
[20181125235202973.png]: /images/20220414/22a1d9e7071c4fd3bd45114af8071ad5.png
[20181125235951175.png]: /images/20220414/816a4f98c6d6459498ad7c88df2e42ed.png
[20181126002205818.png]: /images/20220414/2d3fd6f566e6434c84f4a21a9a77ea69.png