ORACLE DATE和TIMESTAMP資料型別的比較(一) (轉)

ORACLE DATE和TIMESTAMP資料型別的比較(一) (轉)[@more@]

原著作者：James Kmann

如果你想在中DATE和時間資訊的話，實際上你由兩種欄位資料型別的選擇的話，就讓我們看看這兩種資料型別的差別和它們提供了些什麼。:namespace prefix = o ns = "urn:schemas--com::office" />

 

  DATE資料型別

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

> TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;

Date

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

06/20/ 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