oracle常用时间函数汇总

以下内容根据联机文档整理：
1. ADD_MONTH(date,integer) 返回值类型为DATE：
    (1). ADD_MONTH(SYSDATE, 1):：返回当前月的下一个月
    (2). ADD_MONTH(SYSDATE, -1)：返回当前月的下一个月

    (3). ADD_MONTH(SYSDATE,5*12)：返回前五年的那个月

2.CURRENT_DATE：返回session时区所在的当前时间，返回值类型为DATE
   ALTER SESSION SET TIME_ZONE = '-8:0';

   SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

   SESSIONTIMEZONE     CURRENT_DATE

    ---------------                       --------------------

   -08:00                           29-MAY-2000 10:14:33

3. CURRENT_TIMESTAMP(precision)：返回session的本地时间，返回值类型为TIMESTAMP WITH TIME ZONE

  ALTER SESSION SET TIME_ZONE = '-5:0';

  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

  SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

  SESSIONTIMEZONE               CURRENT_TIMESTAMP

  --------------- ---------------------------------------------------     -05:00               04-APR-00 01.17.56.917550 PM -05:00

  
  CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
  INSERT INTO current_test VALUES
  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));

4.LOCALTIMESTAMP(timestamp_precision)：与CURRENT_TIMESTAMP的区别为返回值类型为TIMESTAMP

   SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP(6) FROM DUAL;

   CURRENT_TIMESTAMP                                LOCALTIMESTAMP

  -------------------------------------------------------------------

  04-APR-00 01.27.18.999220 PM -05:00          04-APR-00 01.27.18.999220 PM

  CREATE TABLE local_test (col1TIMESTAMP WITH LOCAL TIME ZONE);
  INSERT INTO local_test
  VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFFPM'));

5.

  SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

  EXTRACT(YEARFROMDATE'1998-03-07')

  ---------------------------------

  1998

  SELECT EXTRACT(month FROM order_date) "Month", COUNT(order_date) "No. of Orders" FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC, "Month"；

6. MONTHS_BETWEEN(date1,date2)：
   SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DD-YYYY'),TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"

   FROM DUAL;

7. NEXT_DAY(date,char)：The argument char must be a day of the week in the date language of your session

   返回2009年10月15日之后的第一个周二所对应的日期是多少：

   SELECT NEXT_DAY('15-OCT-2009','TUESDAY') "NEXT DAY" FROM DUAL;

   NEXT DAY

  --------------------

   20-OCT-2009 00:00:00

8. SYSDATE：返回值类型为DATE
   sysdate+(5/24/60/60) 在系统时间基础上延迟5秒
  sysdate+5/24/60 在系统时间基础上延迟5分钟
  sysdate+5/24 在系统时间基础上延迟5小时
  sysdate+5 在系统时间基础上延迟5天

  SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY, HH24:MI:SS') FROM dual;
  TO_CHAR(SYSDATE,'MONTHDD,YYY
  ----------------------------

  NOVEMBER 05, 2007, 12:34:36

9. SYSTIMESTAMP：返回值类型为TIMESTAMP WITH TIME ZONE

   SELECT SYSTIMESTAMP FROM DUAL;

   SYSTIMESTAMP

  -------------------------------------------------------

  28-MAR-00 12.38.55.538741 PM -08:00

  SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;

  SYSTIMESTAMPATTIMEZONE'UTC'

  ---------------------------------------------------------------------------

  08-07-21 20:39:52,743557 UTC

10. ROUND(date,format)：by default, to the beginning of the nearest day. If you supply an optional format, It is rounded to that format; for example, YYYY rounds date to the first day of the nearest year

     SELECT TO_CHAR(ROUND(TO_DATE('25-MAY-2008 19:45:26','DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS')

     FROM dual;

    TO_CHAR(ROUND(TO_DAT

    --------------------

    25-MAY-2008 20:00:00

11. TRUNC(x[, unit])truncates x. By default, x is truncated to the beginning of the day. If you supply an optional unitstring, x is truncated to that unit

      SELECT TRUNC(TO_DATE('25-MAY-2008'), 'YYYY') FROM dual;

      TRUNC(TO_

      ---------

      01-JAN-08

      SELECT TO_CHAR(TRUNC(TO_DATE('25-MAY-2008 19:45:26','DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS')

      FROM dual;

     TO_CHAR(TRUNC(TO_DAT

     --------------------

     25-MAY-2008 19:00:00
12. 

13. NEW_TIME(date,timezone1,timezone2)：转换时区函数，返回值类型为DATE
     SELECT NEW_TIME(TO_DATE('11-10-09 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') 

    "New Date and Time" FROM DUAL;

     New Date and Time

    --------------------

    09-NOV-2009 21:23:45

14. TO_DATE(x，format)：

      SELECT TO_DATE('July 4, 2007', 'MONTH DD, YYYY') FROM dual;

15. SELECT TO_CHAR(TO_DATE('05-FEB-1968 19:32:36','DD-MON-YYYY HH24:MI:SS'), 'HH24:MI:SS')

     FROM dual;

    TO_CHAR(
     --------

     19:32:36

16. LAST_DAY(date)：返回值为DATE