ORACLE DATE和TIMESTAMP數據類型的比較(一)
2024-08-29 13:31:14
供稿：網友
原著作者：james koopmann
如果你想在oracle中存儲date和時間信息的話，實際上你由兩種字段數據類型的選擇的話，就讓我們看看這兩種數據類型的差別和它們提供了些什么。
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
