Oracle公司內(nèi)部數(shù)據(jù)庫(kù)培訓(xùn)資料16-Oracle9i 日期函數(shù)

1、Oracle9i 日期函數(shù) 目標(biāo)通過(guò)本章章學(xué)習(xí)，您將可可以使用用下列日日期函數(shù)數(shù):TZ_OFFSETCURRENT_DATECURRENT_TIMESTAMPLOCALTIMESTAMPDBTIMEZONESESSIONTIMEZONEEXTRACTFROM_TZTO_TIMESTAMPTO_TIMESTAMP_TZTO_YMINTERVAL時(shí)區(qū)-08:00上圖顯示示了全球球24個(gè)個(gè)時(shí)區(qū)以以及當(dāng)格格林威治治時(shí)間是是12:00時(shí)各時(shí)區(qū)區(qū)的時(shí)差差-05:00+02:00+10:00+07:00Oracle9i日期支持持Oracle9i中, 可以以將時(shí)區(qū)區(qū)加入到到日期和和時(shí)間中中而且可可以將秒秒進(jìn)行

2、進(jìn)進(jìn)一步的的精確日期中加加入了三三種新的的數(shù)據(jù)類類型:TIMESTAMP（時(shí)間撮）TIMESTAMPWITHTIME ZONE(TSTZ) （帶時(shí)區(qū)的的時(shí)間撮撮）TIMESTAMPWITHLOCALTIME ZONE(TSLTZ)（帶有本地地時(shí)區(qū)的的時(shí)間撮撮）Oracle9i支持夏令令時(shí)TZ_OFFSETSELECT TZ_OFFSET(US/Eastern) FROM DUAL;SELECTTZ_OFFSET(Canada/Yukon)FROM DUAL;SELECTTZ_OFFSET(Europe/London)FROMDUAL;顯示時(shí)區(qū)區(qū)US/Eastern的時(shí)差顯示時(shí)區(qū)區(qū)Canada/

3、Yukon的時(shí)差顯示時(shí)區(qū)區(qū)Europe/London的時(shí)差CURRENT_DATECURRENT_DATE對(duì)會(huì)話所所在的時(shí)時(shí)區(qū)是敏敏感的ALTER SESSION SET TIME_ZONE = -8:0;SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;ALTER SESSION SET TIME_ZONE = -5:0;SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;按照當(dāng)前前會(huì)話的的時(shí)區(qū)顯顯示當(dāng)前前會(huì)話的的時(shí)間ALTERSESSIONSETNLS_DATE_FORMAT =DD-MON-YYYY H

4、H24:MI:SS;CURRENT_TIMESTAMPALTERSESSION SETTIME_ZONE=-8:0;SELECTSESSIONTIMEZONE,CURRENT_TIMESTAMPFROM DUAL;ALTERSESSION SETTIME_ZONE=-5:0;SELECTSESSIONTIMEZONE,CURRENT_TIMESTAMPFROM DUAL;按照當(dāng)前前會(huì)話的的時(shí)區(qū)顯顯示當(dāng)前前會(huì)話的的時(shí)間CURRENT_TIMESTAMP對(duì)會(huì)話所所在的時(shí)時(shí)區(qū)是敏敏感的返回值是是TIMESTAMPWITHTIME ZONE數(shù)據(jù)類型型LOCALTIMESTAMPALTERSESSION

5、 SETTIME_ZONE=-5:0;SELECTCURRENT_TIMESTAMP, LOCALTIMESTAMPFROM DUAL;ALTERSESSION SETTIME_ZONE=-8:0;SELECTCURRENT_TIMESTAMP, LOCALTIMESTAMPFROM DUAL;按照當(dāng)前前會(huì)話的的時(shí)區(qū)顯顯示當(dāng)前前會(huì)話的的時(shí)間LOCALTIMESTAMP對(duì)會(huì)話所所在的時(shí)時(shí)區(qū)是敏敏感的返回值是是TIMESTAMP數(shù)據(jù)類型型DBTIMEZONE和SESSIONTIMEZONE SELECTDBTIMEZONEFROM DUAL;SELECTSESSIONTIMEZONE FROMDU

6、AL;顯示數(shù)據(jù)據(jù)庫(kù)所在在的時(shí)區(qū)區(qū)顯示會(huì)話話所在的的時(shí)區(qū)EXTRACTSELECTEXTRACT(YEARFROM SYSDATE) FROMDUAL;SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE)FROM employeesWHERE manager_id = 100;從SYSDATE中抽出年年從HIRE_DATE中抽出月月FROM_TZ應(yīng)用舉例例SELECTFROM_TZ(TIMESTAMP2000-03-2808:00:00,3:00)FROM DUAL;SELECT FROM_TZ(TIMESTAMP 2000-03

7、-28 08:00:00, Australia/North)FROM DUAL; SELECTTO_TIMESTAMP_TZ(1999-12-0111:00:00-8:00,YYYY-MM-DDHH:MI:SSTZH:TZM)FROM DUAL;TO_TIMESTAMP和TO_TIMESTAMP_TZ應(yīng)用舉例例 SELECTTO_TIMESTAMP (2000-12-0111:00:00,YYYY-MM-DDHH:MI:SS)FROM DUAL;TO_YMINTERVAL應(yīng)用舉例例 SELECThire_date,hire_date+TO_YMINTERVAL(01-02) ASHIRE_DATE_YMININTERVALFROM EMPLOYEESWHEREdepartment_id =20;總結(jié)TZ_OFFSETFROM_TZTO_TIMESTAMPTO_TIMESTAMP_TZTO_YMINTERVALCURRENT_DA