ORACLE DATE和TIMESTAMP數據類型的比較
2024-08-29 13:49:47
供稿：網友
DATE數據類型
這個數據類型我們實在是太熟悉了，當我們需要表示日期和時間的話都會想到date類型。它可以存儲月，年，日，世紀，時，分和秒。它典型地用來表示什么時候事情已經發生或將要發生。DATE數據類型的問題在于它表示兩個事件發生時間間隔的度量粒度是秒。這個問題將在文章稍后討論timestamp的時候被解決?？梢允褂肨O_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的時候，需要注重，它們既有類似的地方，更有不同的地方，而足以造成破壞。兩者在簡潔和間隔尺寸方面各有優勢，請合理地選擇。
