【文档说明】Oracle公司内部数据库培训_Les16_chinese课件.ppt，共(14)页，225.000 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92285.html

以下为本文档部分文字说明：

Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9i日期函数16-2Copyright©OracleCorporation,2001.Allr

ightsreserved.目标通过本章学习，您将可以使用下列日期函数:•TZ_OFFSET•CURRENT_DATE•CURRENT_TIMESTAMP•LOCALTIMESTAMP•DBTIMEZONE

•SESSIONTIMEZONE•EXTRACT•FROM_TZ•TO_TIMESTAMP•TO_TIMESTAMP_TZ•TO_YMINTERVAL16-3Copyright©OracleCorporation,2001.Allrightsreserved.时区-08:00上图显示了全球24个时

区以及当格林威治时间是12:00时各时区的时差-05:00+02:00+10:00+07:0016-4Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9i日期支持•Oracle9i中

,可以将时区加入到日期和时间中而且可以将秒进行进一步的精确•日期中加入了三种新的数据类型:–TIMESTAMP（时间撮）–TIMESTAMPWITHTIMEZONE(TSTZ)（带时区的时间撮）–TIMESTAMPWITHLOCALTIMEZONE(TSLTZ)（带有本地时区的时间撮）•

Oracle9i支持夏令时16-5Copyright©OracleCorporation,2001.Allrightsreserved.TZ_OFFSETSELECTTZ_OFFSET('US/Eastern')FROMDUAL;SELECT

TZ_OFFSET('Canada/Yukon')FROMDUAL;SELECTTZ_OFFSET('Europe/London')FROMDUAL;•显示时区‘US/Eastern’的时差•显示时区'Canada/Yukon'的时差•显示时

区'Europe/London'的时差16-6Copyright©OracleCorporation,2001.Allrightsreserved.CURRENT_DATE•CURRENT_DATE对会话所在的

时区是敏感的ALTERSESSIONSETTIME_ZONE='-8:0';SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;ALTERSESSIONSETTIME_ZONE='-5:0';SELECTSESSIO

NTIMEZONE,CURRENT_DATEFROMDUAL;•按照当前会话的时区显示当前会话的时间ALTERSESSIONSETNLS_DATE_FORMAT='DD-MON-YYYYHH24:MI:SS';16-7Copyri

ght©OracleCorporation,2001.Allrightsreserved.CURRENT_TIMESTAMPALTERSESSIONSETTIME_ZONE='-8:0';SELECTSESSIONTIMEZO

NE,CURRENT_TIMESTAMPFROMDUAL;ALTERSESSIONSETTIME_ZONE='-5:0';SELECTSESSIONTIMEZONE,CURRENT_TIMESTAMPFROMDUAL;•按照当前会话的时区显示

当前会话的时间•CURRENT_TIMESTAMP对会话所在的时区是敏感的•返回值是TIMESTAMPWITHTIMEZONE数据类型16-8Copyright©OracleCorporation,2001.Allrightsreserved.LOCALTIM

ESTAMPALTERSESSIONSETTIME_ZONE='-5:0';SELECTCURRENT_TIMESTAMP,LOCALTIMESTAMPFROMDUAL;ALTERSESSIONSETTIME_

ZONE='-8:0';SELECTCURRENT_TIMESTAMP,LOCALTIMESTAMPFROMDUAL;•按照当前会话的时区显示当前会话的时间•LOCALTIMESTAMP对会话所在的时区是敏感的•返回值是TIMESTAMP数据类型16-9Copyri

ght©OracleCorporation,2001.Allrightsreserved.DBTIMEZONE和SESSIONTIMEZONESELECTDBTIMEZONEFROMDUAL;SELECTSESSIONTIMEZONEFROMDU

AL;•显示数据库所在的时区•显示会话所在的时区16-10Copyright©OracleCorporation,2001.Allrightsreserved.EXTRACTSELECTEXTRACT(YEARFROMSYSDATE)FROMDUAL;SELECTlast_name,hi

re_date,EXTRACT(MONTHFROMHIRE_DATE)FROMemployeesWHEREmanager_id=100;•从SYSDATE中抽出年•从HIRE_DATE中抽出月16-11Copyright©OracleCorporation

,2001.Allrightsreserved.FROM_TZ应用举例SELECTFROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')FROMDUAL;SELECTFROM_TZ(TIM

ESTAMP'2000-03-2808:00:00','Australia/North')FROMDUAL;16-12Copyright©OracleCorporation,2001.Allrightsr

eserved.SELECTTO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')FROMDUAL;TO_TIMESTAMP和TO_TIMESTAMP_TZ应用举例SELECTTO_T

IMESTAMP('2000-12-0111:00:00','YYYY-MM-DDHH:MI:SS')FROMDUAL;16-13Copyright©OracleCorporation,2001.Allrightsre

served.TO_YMINTERVAL应用举例SELECThire_date,hire_date+TO_YMINTERVAL('01-02')ASHIRE_DATE_YMININTERVALFROMEMPLOYEESWHEREdepartment_id=20;16-14Copyri

ght©OracleCorporation,2001.Allrightsreserved.总结•TZ_OFFSET•FROM_TZ•TO_TIMESTAMP•TO_TIMESTAMP_TZ•TO_YMINTERVAL•CURRENT_DAT

E•CURRENT_TIMESTAMP•LOCALTIMESTAMP•DBTIMEZONE•SESSIONTIMEZONE•EXTRACT通过本章学习,您已经可以使用: