Oracle 日期和時(shí)間的存儲(chǔ)于處理

本章內(nèi)容包括：

• 處理并存儲(chǔ)一個(gè)特定的日期和時(shí)間。DATE類型可以存儲(chǔ)世紀(jì)、4位年份、月、日、時(shí)、分和秒

• 使用時(shí)間戳timestamp，時(shí)間戳可以存儲(chǔ)一個(gè)特定的日期和時(shí)間。時(shí)間戳的優(yōu)點(diǎn)是可以存儲(chǔ)帶有小數(shù)位的秒，還可以存儲(chǔ)時(shí)區(qū)

• 使用時(shí)間間隔interval，時(shí)間間隔可以存儲(chǔ)時(shí)間的長(zhǎng)度。如1年零3個(gè)月等。

在Oracle數(shù)據(jù)庫(kù)中，默認(rèn)情況下，日期以DD-MON-YYYY格式保存到數(shù)據(jù)庫(kù)中，其中：

• DD是一個(gè)兩位的天數(shù)

• MON是月份中的前三個(gè)字母，如FEB

• YYYY是一個(gè)4位的年份。

默認(rèn)情況下數(shù)據(jù)庫(kù)以DD-MON-YY格式返回日期，其中YY是年份的最后兩位。

1、使用TO_DATE()和TO_CHAR()轉(zhuǎn)換時(shí)間值。

    TO_CHAR(x[,format])用于將時(shí)間值x轉(zhuǎn)化為字符串，該函數(shù)提供一個(gè)可選參數(shù)format來說明x的格式。

SQL> select sysdate from dual;
SYSDATE
-----------
2016/6/9 12
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2016-06-09 12:32:28
SQL> select to_char(sysdate) from dual;
TO_CHAR(SYSDATE)
----------------
09-6月 -16

    TO_DATE(x[,format])用于將字符串x轉(zhuǎn)化為時(shí)間值，該函數(shù)可以指定一個(gè)可選字符串format參數(shù)來說明x的格式。如果沒有指定format參數(shù)，日期就是用默認(rèn)的數(shù)據(jù)庫(kù)格式。x的格式要與format指定的格式一樣。

SQL> select to_char(to_date('05-6月-2016 12:38:23','DD-MON-YYYY HH24:MI:SS'),'HH24:MI:SS') as time
  2  from dual;
TIME
--------
12:38:23

    設(shè)置默認(rèn)的日期格式。默認(rèn)的日期格式是在數(shù)據(jù)庫(kù)參數(shù)NLS_DATE_FORMAT中指定的。可以用下面的語(yǔ)句來修改。

SQL> alter session set nls_date_format = 'YYYY-MM-DD';
Session altered

 2、使用時(shí)間值函數(shù)

    時(shí)間值函數(shù)用于獲取或處理時(shí)間值和時(shí)間戳。

SQL> select sysdate from dual;
SYSDATE
-----------
2016/6/9 12
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDATE,1)
---------------------
2016/7/9 12:52:03
SQL> select add_months(sysdate,-1) from dual;
ADD_MONTHS(SYSDATE,-1)
----------------------
2016/5/9 12:52:15
SQL> select last_day(to_char(sysdate)) from dual;
LAST_DAY(TO_CHAR(SYSDATE))
--------------------------
2016/6/30
SQL> select months_between('09-06月-2016','08-10月-2016') from dual;
select months_between('09-06月-2016','08-10月-2016') from dual
ORA-01861: 文字與格式字符串不匹配
SQL> select months_between('2016-06-09','2016-10-08') from dual;
MONTHS_BETWEEN('2016-06-09','2
------------------------------
             -3.96774193548387
SQL> select months_between('2016-10-09','2016-06-09') from dual;
MONTHS_BETWEEN('2016-10-09','2
------------------------------
                             4

3、使用時(shí)間戳

    CURRENT_TIMESTAMP、LOCALTIMESTAMP、SYSTIMESTAMP返回當(dāng)前回話的日期和時(shí)間以及時(shí)區(qū)

    EXTRACT 從時(shí)間戳類型或者DATE類型中提取并返回年月日時(shí)分秒或時(shí)期

    TO_TIMESTAMP(x[,format])用于將字符串x轉(zhuǎn)化為一個(gè)TIMESTAMP類型，format指定轉(zhuǎn)化類型

4、使用時(shí)間間隔INTERVAL

    舉例：優(yōu)惠券有效期，打折促銷的時(shí)間

    使用INTERVAL_YEAR TO MONTH類型

        創(chuàng)建表coupons用來存儲(chǔ)優(yōu)惠券信息。duration用來記錄優(yōu)惠券有效的時(shí)間間隔。

SQL> create table coupons(
  2  coupon_id integer constraint coups_pk primary key,
  3  name varchar2(30) not null,
  4  duration INTERVAL YEAR(3) TO MONTH
  5  );
Table created

    要想數(shù)據(jù)庫(kù)提供一個(gè)INTERVAL YEAR TO MONTH字面值，可以使用以下語(yǔ)法：

INTERVAL '[+|-][y][-m]' [YEAR[(years_precisiion)]] [TO_MONTH]

    示例：

SQL> INSERT INTO coupons(coupon_id,name,duration)
  2  values(1,'$1 off Z Files',INTERVAL '1' YEAR);
1 row inserted

SQL> INSERT INTO coupons(coupon_id,name,duration)
  2  values(2,'$2 off Pop 3',INTERVAL '11' MONTH);
1 row inserted

SQL> INSERT INTO coupons(coupon_id,name,duration) values(3,'$2 off Pop 31',INTERVAL '2' MONTH);
1 row inserted
SQL> select * from coupons;
                              COUPON_ID NAME                           DURATION
--------------------------------------- ------------------------------ -------------------------------------------------------------------------------
                                      1 $1 off Z Files                 +001-00
                                      2 $2 off Pop 3                   +000-11
                                      3 $2 off Pop 31                  +000-02