Oracle date 和 timestamp 區(qū)別詳解
1.DATE數(shù)據(jù)類型
這個(gè)數(shù)據(jù)類型我們實(shí)在是太熟悉了,當(dāng)我們需要表示日期和時(shí)間的話都會(huì)想到date類型。它可以存儲(chǔ)月,年,日,世紀(jì),時(shí),分和秒。它典型地用來(lái)表示什么時(shí)候事情已經(jīng)發(fā)生或?qū)⒁l(fā)生。
DATE數(shù)據(jù)類型的問(wèn)題在于它表示兩個(gè)事件發(fā)生時(shí)間間隔的度量粒度是秒。這個(gè)問(wèn)題將在稍后討論timestamp的時(shí)候被解決??梢允褂肨O_CHAR函數(shù)把DATE數(shù)據(jù)進(jìn)行傳統(tǒng)地包裝,達(dá)到表示成多種格式的目的。
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
大多數(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è)例子可以更簡(jiǎn)短些,但是我是為了顯示所有的數(shù)字來(lái)強(qiáng)調(diào)計(jì)算方式。
SELECT TO_CHAR(date1, 'MMDDYYYY:HH24:MI:SS') date1,
TO_CHAR(date2, 'MMDDYYYY:HH24:MI:SS') date2,
trunc(86400 * (date2 - date1)) -
60 * (trunc((86400 * (date2 - date1)) / 60)) seconds,
trunc((86400 * (date2 - date1)) / 60) -
60 * (trunc(((86400 * (date2 - date1)) / 60) / 60)) minutes,
trunc(((86400 * (date2 - date1)) / 60) / 60) -
24 * (trunc((((86400 * (date2 - date1)) / 60) / 60) / 24)) hours,
trunc((((86400 * (date2 - date1)) / 60) / 60) / 24) days,
trunc(((((86400 * (date2 - date1)) / 60) / 60) / 24) / 7) weeks
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
2.TIMESTAMP 數(shù)據(jù)類型
DATE數(shù)據(jù)類型的主要問(wèn)題是它粒度不能足夠區(qū)別出兩個(gè)事件哪個(gè)先發(fā)生。ORACLE已經(jīng)在DATE數(shù)據(jù)類型上擴(kuò)展出來(lái)了TIMESTAMP數(shù)據(jù)類型,它包括了所有DATE數(shù)據(jù)類型的年月日時(shí)分秒的信息,而且包括了小數(shù)秒的信息。如果你想把 DATE類型轉(zhuǎn)換成TIMESTAMP類型,就使用CAST函數(shù)。
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
正如你看到的,在轉(zhuǎn)換后的時(shí)間段尾部有了一段“.000000”。這是因?yàn)閺膁ate轉(zhuǎn)換過(guò)來(lái)的時(shí)候,沒有小數(shù)秒的信息,缺省為0。而且顯示格式是按照參數(shù)NLS_TIMESTAMP_FORMAT定的缺省格式顯示。當(dāng)你把一個(gè)表中date類型字段的數(shù)據(jù)移到另一個(gè)表的timestamp類型字段中去的時(shí)候,可以直接寫INSERT SELECT語(yǔ)句,oracle會(huì)自動(dòng)為你做轉(zhuǎn)換的。
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數(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ù)類型更確切。
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
計(jì)算timestamp間的數(shù)據(jù)差別要比老的date數(shù)據(jù)類型更容易。當(dāng)你直接相減的話,看看會(huì)發(fā)生什么。結(jié)果將更容易理解,第一行的17天,18小時(shí),27分鐘和43秒。
SELECT time1,
time2,
substr((time2 - time1), instr((time2 - time1), ' ') + 7, 2) seconds,
substr((time2 - time1), instr((time2 - time1), ' ') + 4, 2) minutes,
substr((time2 - time1), instr((time2 - time1), ' ') + 1, 2) hours,
trunc(to_number(substr((time2 - time1), 1, instr(time2 - time1, ' ')))) days,
trunc(to_number(substr((time2 - time1), 1, instr(time2 - time1, ' '))) / 7) weeks
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
這就意味著不再需要關(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ù)。
SQL> SELECT SYSDATE FROM DUAL;
為了得到系統(tǒng)時(shí)間,返回成timestamp數(shù)據(jù)類型。你可以使用systimpstamp函數(shù)。
SQL> SELECT SYSTIMESTAMP FROM DUAL;
你可以設(shè)置初始化參數(shù)FIXED_DATE指定sysdate函數(shù)返回一個(gè)固定值。這用在測(cè)試日期和時(shí)間敏感的代碼。注意,這個(gè)參數(shù)對(duì)于systimestamp函數(shù)無(wú)效。
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
4.date和timestamp 的區(qū)別
date類型是Oracle常用的日期型變量,他的時(shí)間間隔是秒。兩個(gè)日期型相減得到是兩個(gè)時(shí)間的間隔,注意單位是“天”。例如:查看一下當(dāng)前距離倫敦奧運(yùn)會(huì)開幕還有多長(zhǎng)時(shí)間:
select to_date('2012-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss')-sysdate from dual
結(jié)果是:92.2472685185185天,然后你根據(jù)相應(yīng)的時(shí)間換算你想要的間隔就行!這個(gè)結(jié)果可能對(duì)程序員有用,對(duì)于想直接看到結(jié)果的人,這個(gè)數(shù)字還不是很直觀,所以,就引出了timestamp類型
timestamp是DATE類型的擴(kuò)展,可以精確到小數(shù)秒(fractional_seconds_precision),可以是0 to9,缺省是6。兩個(gè)timestamp相減的話,不能直接的得到天數(shù)書,而是得到,
多少天,多少小時(shí),多少秒等,例如:同樣查看一下當(dāng)前距離倫敦奧運(yùn)會(huì)開幕還有多長(zhǎng)時(shí)間.
select to_timestamp('2012-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss')-systimestamp from dual
結(jié)果是:+000000092 05:51:24.032000000,稍加截取,就可以得到92天5小時(shí),51分鐘,24秒,這樣用戶看起來(lái)比較直觀一些!但是這個(gè)數(shù)字對(duì)程序員來(lái)說(shuō)不是很直觀了,如果想要具體的時(shí)間長(zhǎng)度的話,并且精度不要求到毫秒的話,可以將timestamp類型轉(zhuǎn)成date類型,然后直接相減即可。
5.date和timestamp之間的相互轉(zhuǎn)換可以通過(guò)
to_char來(lái)轉(zhuǎn)換timestamp——>date:
select to_date(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual
date ——>timestamp:
select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual
當(dāng)使用date和timestamp類型的時(shí)候,選擇是很清楚的。你可以隨意處置date和timestamp類型。當(dāng)你試圖轉(zhuǎn)換到更強(qiáng)大的timestamp的時(shí)候,需要注意,它們既有類似的地方,更有不同的地方,而足以造成破壞。兩者在簡(jiǎn)潔和間隔尺寸方面各有優(yōu)勢(shì),請(qǐng)合理地選擇。
感謝閱讀,希望能幫助到大家,謝謝大家對(duì)本站的支持!
您可能感興趣的文章:- Oracle數(shù)據(jù)庫(kù)下載及安裝圖文操作步驟
- Oracle存儲(chǔ)過(guò)程基本語(yǔ)法介紹
- ORACLE 如何查詢被鎖定表及如何解鎖釋放session
- oracle常用sql語(yǔ)句
- oracle sqlplus 常用命令大全
- ORACLE常用數(shù)值函數(shù)、轉(zhuǎn)換函數(shù)、字符串函數(shù)
- oracle 創(chuàng)建表空間詳細(xì)介紹
- Oracle新建用戶、角色,授權(quán),建表空間的sql語(yǔ)句
- Oracle正則表達(dá)式使用介紹