oracle 的時間處理

SQL> Select sysdate from dual;
SYSDATE
----------
21-6
SQL> Select last_day(sysdate) from dual;

LAST_DAY(S
----------
30-6
date_value:=add_months(date_value,number_of_months)

SQL> Select add_months(sysdate,2) from dual;

ADD_MONTHS
----------
21-8
SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))fromdual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD'))
----------------------------------------------------------
                                                 -4.6966741

SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual;

NEXT_DAY(T
----------
26-6
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;

SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11  
SQL> alter session set time_zone='-11:00' 2 /
  
SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11
7SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-6
8SQL> select dbtimezone from dual;

DBTIME
------
-08:00

9date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;

This Month
----------
         6

SQL> select extract(year from add_months(sysdate,36)) " Years"from dual;

     Years
----------
       2008

10SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------