oracle数据库ppt+中科院培训专用Les16_cn

Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9i/10g日期时间函数16-2Copyright©OracleCorporation,2001.Allrightsreserved.目标完成本课后,您应当能够使用下面的日期时间函数：•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图中表示当格林尼治时间是12:00时，其它时区的时间。-05:00+02:00+10:00+07:0016-4Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9i日期时间支持•在Oracle9i中，你能够在你的日期和时间数据中包含时区，并且提供对小数秒的支持•3种新的日期时间数据类型：–TIMESTAMP–TIMESTAMPWITHTIMEZONE(TSTZ)–TIMESTAMPWITHLOCALTIMEZONE(TSLTZ)•Oracle9i服务器提供对日期时间数据类型的夏令时支持16-6Copyright©OracleCorporation,2001.Allrightsreserved.TZ_OFFSETSELECTTZ_OFFSET('US/Eastern')FROMDUAL;SELECTTZ_OFFSET('Canada/Yukon')FROMDUAL;SELECTTZ_OFFSET('Europe/London')FROMDUAL;•显示对时区'US/Eastern'的时区偏移量•显示对时区'Canada/Yukon'的时区偏移量•显示对时区'Europe/London'的时区偏移量16-8Copyright©OracleCorporation,2001.Allrightsreserved.CURRENT_DATE•CURRENT_DATE对于会话时区是敏感的•在罗马日历中返回值是日期ALTERSESSIONSETTIME_ZONE='-8:0';SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;ALTERSESSIONSETTIME_ZONE='-5:0';SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;•显示在会话时区中的当前日期和时间ALTERSESSIONSETNLS_DATE_FORMAT='DD-MON-YYYYHH24:MI:SS';16-9Copyright©OracleCorporation,2001.Allrightsreserved.CURRENT_TIMESTAMPALTERSESSIONSETTIME_ZONE='-8:0';SELECTSESSIONTIMEZONE,CURRENT_TIMESTAMPFROMDUAL;ALTERSESSIONSETTIME_ZONE='-5:0';SELECTSESSIONTIMEZONE,CURRENT_TIMESTAMPFROMDUAL;•显示在会话时区中的当前日期和时间，包含小数秒•CURRENT_TIMESTAMP对于会话时区是敏感的•返回值是TIMESTAMPWITHTIMEZONE数据类型16-10Copyright©OracleCorporation,2001.Allrightsreserved.LOCALTIMESTAMPALTERSESSIONSETTIME_ZONE='-5:0';SELECTCURRENT_TIMESTAMP,LOCALTIMESTAMPFROMDUAL;ALTERSESSIONSETTIME_ZONE='-8:0';SELECTCURRENT_TIMESTAMP,LOCALTIMESTAMPFROMDUAL;•以TIMESTAMP数据类型的值显示在会话时区中的当前日期和时间•LOCALTIMESTAMP返回一个TIMESTAMP值，而CURRENT_TIMESTAMP返回一个TIMESTAMPWITHTIMEZONE值16-11Copyright©OracleCorporation,2001.Allrightsreserved.DBTIMEZONE和SESSIONTIMEZONESELECTDBTIMEZONEFROMDUAL;SELECTSESSIONTIMEZONEFROMDUAL;•显示数据库时区的值•显示会话时区的值16-12Copyright©OracleCorporation,2001.Allrightsreserved.EXTRACTSELECTEXTRACT(YEARFROMSYSDATE)FROMDUAL;SELECTlast_name,hire_date,EXTRACT(MONTHFROMHIRE_DATE)FROMemployeesWHEREmanager_id=100;•从SYSDATE中显示年•从HIRE_DATE中显示MANAGER_ID是100的雇员参加工作的月份16-13Copyright©OracleCorporation,2001.Allrightsreserved.用FROM_TZ转换TIMESTAMPSELECTFROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')FROMDUAL;•显示TIMESTAMP值'2000-03-2808:00:00'作为一个TIMESTAMPWITHTIMEZONE值SELECTFROM_TZ(TIMESTAMP'2000-03-2808:00:00','Australia/North')FROMDUAL;•对于时区'Australia/North'显示TIMESTAMP值'2000-03-2808:00:00'作为一个TIMESTAMPWITHTIMEZONE值16-14Copyright©OracleCorporation,2001.Allrightsreserved.SELECTTO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')FROMDUAL;用TO_TIMESTAMP和TO_TIMESTAMP_TZ转换STRING到TIMESTAMPSELECTTO_TIMESTAMP('2000-12-0111:00:00','YYYY-MM-DDHH:MI:SS')FROMDUAL;•显示字符串'2000-12-0111:00:00'作为TIMESTAMP值•显示字符串'1999-12-0111:00:00-8:00'作为TIMESTAMPWITHTIMEZONE值16-15Copyright©OracleCorporation,2001.Allrightsreserved.用TO_YMINTERVAL转换时间间隔SELECThire_date,hire_date+TO_YMINTERVAL('01-02')ASHIRE_DATE_YMININTERVALFROMEMPLOYEESWHEREdepartment_id=20;•显示一个日期，该日期是，工作在DEPARTMENT_ID是20的部门的雇员，在受雇之后一年两个月的日期16-16Copyright©OracleCorporation,2001.Allrightsreserved.小结•TZ_OFFSET•FROM_TZ•TO_TIMESTAMP•TO_TIMESTAMP_TZ•TO_YMINTERVAL•CURRENT_DATE•CURRENT_TIMESTAMP•LOCALTIMESTAMP•DBTIMEZONE•SESSIONTIMEZONE•EXTRACT在本课中,您应该已经学会如何使用下面的函数：16-17Copyright©OracleCorporation,2001.Allrightsreserved.练习16概览本章练习包括使用Oracle9i日期时间函数：