Oracle中常用的函数——时间类型函数

1. 日期的处理（RR 与 yyyy）
默认情况下，在英国和美国的设备中的日期格式为RR制：DD-MON-RR

设置变量nls_date_format可以改变日期格式，如：
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd HH24:mi:ss';

RR制 与 yyyy制 的转换

                        指定日期
                00 - 49            50 - 99
当    
前    00 - 49        本世纪            上世纪
    
日    50 - 99        下世纪            本世纪
期    

例：若当前为2020年，27-DEC-20转换为yyyy制，表示2020年12月27日
                    27-DEC-98转换为yyyy制，表示1998年12月27日
                    
    若当前为1995年，27-DEC-20转换为yyyy制，表示2020年12月27日，不是1920年12月27日
                    27-DEC-98转换为yyyy制，表示1998年12月27日

SELECT TO_CHAR(TO_DATE('27-12月-98' , 'DD-MON-RR') , 'yyyy-mm-dd') FROM dual;

TO_CHAR(TO_DATE('27-12月-98' , 'DD-MON-RR') , 'yyyy-mm-dd')
------------
1998-12-27

SELECT TO_CHAR(TO_DATE('27-12月-20' , 'DD-MON-RR') , 'yyyy-mm-dd') FROM dual;

TO_CHAR(TO_DATE('27-12月-20' , 'DD-MON-RR') , 'yyyy-mm-dd')
------------
2020-12-27

2. SYSDATE 从用户会话返回当前的日期时间
例：
SQL> SELECT SYSDATE FROM dual;

SYSDATE
-------------------
2020-12-27 01:50:19

3. CURRENT_DATE 从用户会话返回当前日期时间
例：
SQL> SELECT SESSIONTIMEZONE AS USERTIMEZONE , CURRENT_DATE FROM dual;

USERTIMEZONE
---------------------------------------------------------------------------
CURRENT_DATE
-------------------
+08:00
2020-12-27 02:01:42

4. CURRENT_TIMESTAMP 从用户会话返回当前日期时间和时区
例：
SQL> SELECT SESSIONTIMEZONE AS USERTIMEZONE , CURRENT_TIMESTAMP FROM dual;

USERTIMEZONE
---------------------------------------------------------------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
+08:00
27-DEC-20 02.02.21.945544 AM +08:00

5. 日期的计算
对日期使用算术运算符：

例：
SQL> SELECT
    emp.EMPLOYEE_ID , emp.FIRST_NAME , emp.LAST_NAME , TO_CHAR(emp.HIRE_DATE,'yyyy-mm-dd') ,TO_CHAR(CURRENT_DATE,'yyyy-mm-dd') ,
    ROUND((CURRENT_DATE - emp.HIRE_DATE)/365) AS EMPLOYED_YEAR
FROM employees emp OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID FIRST_NAME         LAST_NAME           HIRE_DATE    CURRENT_DATE        EMPLOYED_YEAR    
----------- -------------------- ------------------------- ------------------- -------------------
    105 David         Austin            2005-06-25 00:00:00    2020-12-27 02:27:09           16
    106 Valli         Pataballa           2006-02-05 00:00:00    2020-12-27 02:27:09           15
    107 Diana         Lorentz           2007-02-07 00:00:00    2020-12-27 02:27:09           14
    108 Nancy         Greenberg           2002-08-17 00:00:00    2020-12-27 02:27:09           18
    109 Daniel         Faviet            2002-08-16 00:00:00    2020-12-27 02:27:09           18

6. 日期处理函数
(1) MONTHS_BETWEEN(d1 , d2)：计算两个日期之间的月数差值
注意：MONTHS_BETWEEN(d1 , d2) 函数不会自动取整

例：
SQL> SELECT MONTHS_BETWEEN(TO_DATE('01-SEP-05' , 'DD-MON-RR') , TO_DATE('11-JAN-04' , 'DD-MON-RR')) FROM dual;

MONTHS_BETWEEN(TO_DATE('01-SEP-05','DD-MON-RR'),TO_DATE('11-JAN-04','DD-MON-RR')
--------------------------------------------------------------------------------
                                      19.6774194

(2) ADD_MONTHS(d , n): 返回日期d加上n个月以后的日期
说明：d和n都是必须的，d为日期，n为数值
例：
SQL> SELECT ADD_MONTHS(TO_DATE('03-SEP-05' , 'DD-MON-RR') , 2) FROM dual;

ADD_MONTHS(TO_DATE('03-SEP-05' , 'DD-MON-RR') , 2)
-------------------
2005-11-03 00:00:00

(3)NEXT_DAY(d , c): 返回日期d之后的c代表的一天第一次出现的日期
说明：d和c都是必须的，d为日期，c为一周中某一天的文本应用
例：返回2021-11-15（星期一）的之后的第一个周一
SQL> SELECT TO_CHAR(NEXT_DAY(TO_DATE('2021-11-15','yyyy-mm-dd'),'MONDAY'),'yyyy-mm-dd') "Next_Monday" FROM dual;

Next_Monday
-------------------
2021-11-22

(4) LAST_DAY(d): 返回日期d所属的月份的最后一天
例：
SQL> SELECT TO_CHAR(SYSDATE , 'yyyy-mm-dd') , TO_CHAR(LAST_DAY(SYSDATE) , 'yyyy-mm-dd') FROM dual;

TO_CHAR(SYSDATE , 'yyyy-mm-dd')     TO_CHAR(LAST_DAY(SYSDATE) , 'yyyy-mm-dd')
-------------------------------     ---------------------------------------
2020-12-27                          2020-12-31

(5) ROUND(d , 'MONTH') / ROUND(d , 'YEAR')：对 日期d 按照月/年进行四舍五入
说明：
对日期类型按月四舍五入时，如果小于等于15号，则返回当月1号零点零分；如果大于等于16号，则返回下月1日零点零分
对日期类型按年四舍五入时，如果小于等于6月30日，则返回当年1月1日的零点零分；如果大于等于7月1日，则返回下一年1月1日的零点零分
例：
SQL> SELECT ROUND(TO_DATE('2020-12-15 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'MONTH') FROM dual;

ROUND(TO_DATE('2020-12-15 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'MONTH')
----------------------------------------------------------------------------
2020-12-01 00:00:00

SQL> SELECT ROUND(TO_DATE('2020-12-16 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'MONTH') FROM dual;

ROUND(TO_DATE('2020-12-16 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'MONTH')
----------------------------------------------------------------------------
2021-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE('2020-06-30 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'YEAR') FROM dual;

ROUND(TO_DATE('2020-06-30 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'YEAR')
----------------------------------------------------------------------------
2020-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE('2020-07-01 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'YEAR') FROM dual;

ROUND(TO_DATE('2020-07-01 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'YEAR')
-------------------
2021-01-01 00:00:00

(6) TRUNC(d,'MONTH') / TRUNC(d,'YEAR'): 对日期d按照年月进行截断
例：
SQL> SELECT TRUNC(TO_DATE('2020-06-30 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'MONTH') FROM dual;

TRUNC(TO_DATE('2020-06-30 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'MONTH')
----------------------------------------------------------------------------
2020-06-01 00:00:00

SQL> SELECT TRUNC(TO_DATE('2020-12-27 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'YEAR') FROM dual;

TRUNC(TO_DATE('2020-12-27 03:23:16' , 'yyyy-mm-dd hh24:mi:ss') , 'YEAR')
----------------------------------------------------------------------------
2020-01-01 00:00:00