Oracle date 和 timestamp 區(qū)別

https://www.cnblogs.com/java-class/p/4742740.html

1.DATE數(shù)據(jù)類型

     這個(gè)數(shù)據(jù)類型我們實(shí)在是太熟悉了，當(dāng)我們需要表示日期和時(shí)間的話都會(huì)想到date類型。它可以存儲(chǔ)月，年，日，世紀(jì)，時(shí)，分和秒。它典型地用來表示什么時(shí)候事情已經(jīng)發(fā)生或?qū)⒁l(fā)生。
     DATE數(shù)據(jù)類型的問題在于它表示兩個(gè)事件發(fā)生時(shí)間間隔的度量粒度是秒。這個(gè)問題將在稍后討論timestamp的時(shí)候被解決。可以使用TO_CHAR函數(shù)把DATE數(shù)據(jù)進(jìn)行傳統(tǒng)地包裝，達(dá)到表示成多種格式的目的。

1 SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;2 3 Date 
4 --------------------------- 5 06/20/2003 16:55:14 6 06/26/2003 11:16:36

   大多數(shù)人陷入的麻煩就是計(jì)算兩個(gè)時(shí)間間的間隔年數(shù)、月數(shù)、天數(shù)、小時(shí)數(shù)和秒數(shù)。你需要明白的是，當(dāng)你進(jìn)行兩個(gè)日期的相減運(yùn)算的時(shí)候，得到的是天數(shù)。 你需要乘上每天的秒數(shù)（1天=86400秒），然后，你可以再次計(jì)算得到你想要的間隔數(shù)。下面就是我的解決方法，可以精確計(jì)算出兩個(gè)時(shí)間的間隔。我明白這個(gè)例子可以更簡短些，但是我是為了顯示所有的數(shù)字來強(qiáng)調(diào)計(jì)算方式。

 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) weeks11   FROM date_table12 13 DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS 
14 ----------------- ----------------- ---------- ---------- ---------- ---------- ---------- 15 06202003:16:55:14 07082003:11:22:57 43 27 18 17 2 16 06262003:11:16:36 07082003:11:22:57 21 6 0 12 1

2.TIMESTAMP 數(shù)據(jù)類型

    DATE數(shù)據(jù)類型的主要問題是它粒度不能足夠區(qū)別出兩個(gè)事件哪個(gè)先發(fā)生。ORACLE已經(jīng)在DATE數(shù)據(jù)類型上擴(kuò)展出來了TIMESTAMP數(shù)據(jù)類型，它包括了所有DATE數(shù)據(jù)類型的年月日時(shí)分秒的信息，而且包括了小數(shù)秒的信息。如果你想把   DATE類型轉(zhuǎn)換成TIMESTAMP類型，就使用CAST函數(shù)。

1 SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t; 
2 Date 
3 ----------------------------------------------------- 4 20-JUN-03 04.55.14.000000 PM 
5 26-JUN-03 11.16.36.000000 AM

     正如你看到的，在轉(zhuǎn)換后的時(shí)間段尾部有了一段“.000000”。這是因?yàn)閺膁ate轉(zhuǎn)換過來的時(shí)候，沒有小數(shù)秒的信息，缺省為0。而且顯示格式是按照參數(shù)NLS_TIMESTAMP_FORMAT定的缺省格式顯示。當(dāng)你把一個(gè)表中date類型字段的數(shù)據(jù)移到另一個(gè)表的timestamp類型字段中去的時(shí)候，可以直接寫INSERT SELECT語句，oracle會(huì)自動(dòng)為你做轉(zhuǎn)換的。

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table 
2 Date 
3 ------------------- 4 06/20/2003 16:55:14 5 06/26/2003 11:16:36

    TIMESTAMP數(shù)據(jù)的格式化顯示和DATE 數(shù)據(jù)一樣。注意，to_char函數(shù)支持date和timestamp，但是trunc卻不支持TIMESTAMP數(shù)據(jù)類型。這已經(jīng)清楚表明了在當(dāng)兩個(gè)時(shí)間的差別極度重要的情況下，使用TIMESTAMP數(shù)據(jù)類型要比DATE數(shù)據(jù)類型更確切。

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table2 3 Date 
4 ----------------------- 5 06/20/2003 16:55:14:000 6 06/26/2003 11:16:36:000

     計(jì)算timestamp間的數(shù)據(jù)差別要比老的date數(shù)據(jù)類型更容易。當(dāng)你直接相減的話，看看會(huì)發(fā)生什么。結(jié)果將更容易理解，第一行的17天，18小時(shí)，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 9   10 TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS 
11 ------------------------- -------------------------- ------- ------- ----- ---- ----- 12 06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2 13 06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1

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

3.系統(tǒng)日期和時(shí)間

     為了得到系統(tǒng)時(shí)間，返回成date數(shù)據(jù)類型。你可以使用sysdate函數(shù)。

1 SQL> SELECT SYSDATE FROM DUAL;

     為了得到系統(tǒng)時(shí)間，返回成timestamp數(shù)據(jù)類型。你可以使用systimpstamp函數(shù)。

1 SQL> SELECT SYSTIMESTAMP FROM DUAL;

     你可以設(shè)置初始化參數(shù)FIXED_DATE指定sysdate函數(shù)返回一個(gè)固定值。這用在測試日期和時(shí)間敏感的代碼。注意，這個(gè)參數(shù)對于systimestamp函數(shù)無效。

 1 SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00'; 
 2 System altered. 3  4 SQL> select sysdate from dual; 
 5 SYSDATE 
 6 ---------  7 01-JAN-03 8  9 SQL> select systimestamp from dual; 
10 SYSTIMESTAMP 
11 --------------------------------------------------------- 12 09-JUL-03 11.05.02.519000 AM -06:00

4.date和timestamp 的區(qū)別

     date類型是Oracle常用的日期型變量，他的時(shí)間間隔是秒。兩個(gè)日期型相減得到是兩個(gè)時(shí)間的間隔，注意單位是“天”。例如：查看一下當(dāng)前距離倫敦奧運(yùn)會(huì)開幕還有多長時(shí)間：

1 select to_date('2012-7-28 03:12:00','yyyy-mm-dd hh34:mi:ss')-sysdate from dual

結(jié)果是：92.2472685185185天，然后你根據(jù)相應(yīng)的時(shí)間換算你想要的間隔就行！這個(gè)結(jié)果可能對程序員有用，對于想直接看到結(jié)果的人，這個(gè)數(shù)字還不是很直觀，所以，就引出了timestamp類型

timestamp是DATE類型的擴(kuò)展，可以精確到小數(shù)秒（fractional_seconds_precision），可以是0 to9，缺省是６。兩個(gè)timestamp相減的話，不能直接的得到天數(shù)書，而是得到，
多少天，多少小時(shí)，多少秒等，例如：同樣查看一下當(dāng)前距離倫敦奧運(yùn)會(huì)開幕還有多長時(shí)間.

1 select to_timestamp('2012-7-28 03:12:00','yyyy-mm-dd hh34:mi:ss')-systimestamp from dual

     結(jié)果是：+000000092 05:51:24.032000000，稍加截取，就可以得到92天5小時(shí)，51分鐘，24秒，這樣用戶看起來比較直觀一些！但是這個(gè)數(shù)字對程序員來說不是很直觀了，如果想要具體的時(shí)間長度的話，并且精度不要求到毫秒的話，可以將timestamp類型轉(zhuǎn)成date類型，然后直接相減即可。

5.date和timestamp之間的相互轉(zhuǎn)換可以通過

to_char來轉(zhuǎn)換 timestamp——>date：

1 select to_date(to_char(systimestamp,'yyyy-mm-dd hh34:mi:ss'),'yyyy-mm-dd hh34:mi:ss') from dual

date ——>timestamp：

1 select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh34:mi:ss'),'yyyy-mm-dd hh34:mi:ss') from dual

     當(dāng)使用date和timestamp類型的時(shí)候，選擇是很清楚的。你可以隨意處置date和timestamp類型。當(dāng)你試圖轉(zhuǎn)換到更強(qiáng)大的timestamp的時(shí)候，需要注意，它們既有類似的地方，更有不同的地方，而足以造成破壞。兩者在簡潔和間隔尺寸方面各有優(yōu)勢，請合理地選擇。

作者： Orson            
出處： http://www.cnblogs.com/java-class/
如果，您認(rèn)為閱讀這篇博客讓您有些收獲，不妨點(diǎn)擊一下右下角的 【推薦】
如果，您希望更容易地發(fā)現(xiàn)我的新博客，不妨點(diǎn)擊一下左下角的 【關(guān)注我】
如果，您對我的博客內(nèi)容感興趣，請繼續(xù)關(guān)注我的后續(xù)博客，我是 【Orson】

本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段 聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責(zé)任的權(quán)利。