oracle資料型別date和timestamp的轉化

轉載：http://hi.baidu.com/ufobject/blog/item/f656de17d724d103c93d6d91.html

當您想在oracle中儲存date和時間資訊的時候，你會用DATE還是TIMESTAMP呢?下面就讓我們看看這兩種資料型別的差別和他們提供了些什麼。

date資料型別

這個資料型別我們實在是太熟悉了，當我們需要表示日期和時間的話都會想到date型別。他能夠儲存月，年，日，世紀，時，分和秒。他典型地用來表示什麼時候事情已發生或將要發生。date資料型別的問題在於他表示兩個事件發生時間間隔的度量粒度是秒。這個問題將在文章稍後討論timestamp的時候被解決。能夠使用to_char函式把date資料進行傳統地包裝，達到表示成多種格式的目的。

sql> select to_char(date1,mm/dd/yyyy hh24:mi:ss) "date" from date_table;

date

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

06/20/2003 16:55:14

06/26/2003 11:16:36



我見到的大多數人陷入的麻煩就是計算兩個時間間的間隔年數、月數、天數、小時數和秒數。您需要明白的是，當您進行兩個日期的相減運算的時候，得到的是天數。您需要乘上每天的秒數（1天=86400秒），然後，您能夠再次計算得到您想要的間隔數。下面就是我的解決方法，能夠精確計算出兩個時間的間隔。我明白這個例子能夠更簡短些，但是我是為了顯示任何的數字來強調計算方式。

1         select to_char(date1,mmddyyyy:hh24:mi:ss) date1,

2         to_char(date2,mmddyyyy:hh24:mi:ss) date2,

3         trunc(86400*(date2-date1))-

4         60*(trunc((86400*(date2-date1))/60)) seconds,

5         trunc((86400*(date2-date1))/60)-

6         60*(trunc(((86400*(date2-date1))/60)/60)) minutes,

7         trunc(((86400*(date2-date1))/60)/60)-

8         24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,

9         trunc((((86400*(date2-date1))/60)/60)/24) days,

10         trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks

11*        from date_table

date1             date2                seconds    minutes     hours      days      weeks

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

06202003:16:55:14 07082003:11:22:57         43         27         18        17          2

06262003:11:16:36 07082003:11:22:57         21           6          0        12          1



timestamp 資料型別

date資料型別的主要問題是他粒度不能足夠區別出兩個事件哪個先發生。oracle已在date資料型別上擴充套件出來了timestamp資料型別，他包括了任何date資料型別的年月日時分秒的資訊，而且包括了小數秒的資訊。假如您想把date型別轉換成timestamp型別，就使用cast函式。

sql> select cast(date1 as timestamp) "date" from t;

date

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

20-jun-03 04.55.14.000000 pm

26-jun-03 11.16.36.000000 am



正如您看到的，在轉換後的時間段尾部有了一段“.000000”。這是因為從date轉換過來的時候，沒有小數秒的資訊，預設為0。而且顯示格式是按照引數nls_timestamp_format定的預設格式顯示。當您把一個表中date型別欄位的資料移到另一個表的timestamp型別欄位中去的時候，能夠直接寫insert select語句，oracle會自動為您做轉換的。

1 select to_char(time1,mm/dd/yyyy hh24:mi:ss) "date" from date_table

date

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

06/20/2003 16:55:14

06/26/2003 11:16:36


timestamp資料的格式化顯示和date 資料相同。注意，to_char函式支援date和timestamp，但是trunc卻不支援timestamp資料型別。這已清楚表明了在當兩個時間的差別極度重要的情況下，使用timestamp資料型別要比date資料型別更確切。

假如您想顯示timestamp的小數秒資訊，參考下面：

1 select to_char(time1,mm/dd/yyyy hh24:mi:ss:ff3) "date" from date_table

date

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

06/20/2003 16:55:14:000

06/26/2003 11:16:36:000

在上例中，我只現實了小數點後3位的內容。

計算timestamp間的資料差別要比老的date資料型別更容易。當您直接相減的話，看看會發生什麼。結果將更容易理解，第一行的17天，18小時，27分鐘和43秒。

1 select time1,

2         time2,

3         substr((time2-time1),instr((time2-time1), )+7,2)                 seconds,

4         substr((time2-time1),instr((time2-time1), )+4,2)                 minutes,

5         substr((time2-time1),instr((time2-time1), )+1,2)                 hours,

6         trunc(to_number(substr((time2-time1),1,instr(time2-time1, ))))   days,

7         trunc(to_number(substr((time2-time1),1,instr(time2-time1, )))/7) weeks

8*   from date_table

time1                       time2                      seconds minutes hours days weeks

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

06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43      27      18    17    2

06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21     06      00    12    1

這就意味著不再需要關心一天有多少秒在麻煩的計算中。因此，得到天數、月數、天數、時數、分鐘數和秒數就成為用substr函式摘取出數字的事情了。

系統日期和時間

為了得到系統時間，返回成date資料型別。您能夠使用sysdate函式。

sql> select sysdate from dual;

為了得到系統時間，返回成timestamp資料型別。您能夠使用systimpstamp函式。

sql> select systimestamp from dual;

您能夠配置初始化引數fixed_date指定sysdate函式返回一個固定值。這用在測試日期和時間敏感的程式碼。注意，這個引數對於systimestamp函式無效。

sql> alter system set fixed_date = 2003-01-01-10:00:00;

system altered.



sql> select sysdate from dual;

sysdate

---------

01-jan-03



sql> select systimestamp from dual;

systimestamp

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

09-jul-03 11.05.02.519000 am -06:00

當使用date和timestamp型別的時候，選擇是很清楚的。您能夠隨意處置date和timestamp型別。當您試圖轉換到更強大的timestamp的時候，需要注意，他們既有類似的地方，更有不同的地方，而足以造成破壞。兩者在簡潔和間隔尺寸方面各有優勢，請合理地選擇。