Oracle-OCP-SQL-16-Oracle9i-Datetime-Functions

Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iDatetimeFunctions16-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeableusethefollowingdatetimefunctions:•TZ_OFFSET•CURRENT_DATE•CURRENT_TIMESTAMP•LOCALTIMESTAMP•DBTIMEZONE•SESSIONTIMEZONE•EXTRACT•FROM_TZ•TO_TIMESTAMP•TO_TIMESTAMP_TZ•TO_YMINTERVAL16-3Copyright©OracleCorporation,2001.Allrightsreserved.TIMEZONES-08:00TheimagerepresentsthetimeforeachtimezonewhenGreenwichtimeis12:00.-05:00+02:00+10:00+07:0016-4Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iDatetimeSupport•InOracle9i,youcanincludethetimezoneinyourdateandtimedata,andprovidesupportforfractionalseconds.•ThreenewdatatypesareaddedtoDATE:–TIMESTAMP–TIMESTAMPWITHTIMEZONE(TSTZ)–TIMESTAMPWITHLOCALTIMEZONE(TSLTZ)•Oracle9iprovidesdaylightsavingssupportfordatetimedatatypesintheserver.16-6Copyright©OracleCorporation,2001.Allrightsreserved.TZ_OFFSETSELECTTZ_OFFSET('US/Eastern')FROMDUAL;SELECTTZ_OFFSET('Canada/Yukon')FROMDUAL;SELECTTZ_OFFSET('Europe/London')FROMDUAL;•Displaythetimezoneoffsetforthetimezone'US/Eastern'•Displaythetimezoneoffsetforthetimezone'Canada/Yukon'•Displaythetimezoneoffsetforthetimezone'Europe/London'16-8Copyright©OracleCorporation,2001.Allrightsreserved.CURRENT_DATE•CURRENT_DATEissensitivetothesessiontimezone.•ThereturnvalueisadateintheGregoriancalendar.ALTERSESSIONSETTIME_ZONE='-8:0';SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;ALTERSESSIONSETTIME_ZONE='-5:0';SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;•Displaythecurrentdateandtimeinthesession’stimezone.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;•Displaythecurrentdateandfractionaltimeinthesession'stimezone.•CURRENT_TIMESTAMPissensitivetothesessiontimezone.•ThereturnvalueisoftheTIMESTAMPWITHTIMEZONEdatatype.16-10Copyright©OracleCorporation,2001.Allrightsreserved.LOCALTIMESTAMPALTERSESSIONSETTIME_ZONE='-5:0';SELECTCURRENT_TIMESTAMP,LOCALTIMESTAMPFROMDUAL;ALTERSESSIONSETTIME_ZONE='-8:0';SELECTCURRENT_TIMESTAMP,LOCALTIMESTAMPFROMDUAL;•DisplaythecurrentdateandtimeinthesessiontimezoneinavalueofTIMESTAMPdatatype.•LOCALTIMESTAMPreturnsaTIMESTAMPvalue,whereasCURRENT_TIMESTAMPreturnsaTIMESTAMPWITHTIMEZONEvalue.16-11Copyright©OracleCorporation,2001.Allrightsreserved.DBTIMEZONEandSESSIONTIMEZONESELECTDBTIMEZONEFROMDUAL;SELECTSESSIONTIMEZONEFROMDUAL;•Displaythevalueofthedatabasetimezone.•Displaythevalueofthesession'stimezone.16-12Copyright©OracleCorporation,2001.Allrightsreserved.EXTRACTSELECTEXTRACT(YEARFROMSYSDATE)FROMDUAL;SELECTlast_name,hire_date,EXTRACT(MONTHFROMHIRE_DATE)FROMemployeesWHEREmanager_id=100;•DisplaytheYEARcomponentfromtheSYSDATE.•DisplaytheMONTHcomponentfromtheHIRE_DATEforthoseemployeeswhoseMANAGER_IDis100.16-13Copyright©OracleCorporation,2001.Allrightsreserved.TIMESTAMPConversionUsingFROM_TZSELECTFROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')FROMDUAL;•DisplaytheTIMESTAMPvalue'2000-03-2808:00:00'asaTIMESTAMPWITHTIMEZONEvalue.SELECTFROM_TZ(TIMESTAMP'2000-03-2808:00:00','Australia/North')FROMDUAL;•DisplaytheTIMESTAMPvalue'2000-03-2808:00:00'asaTIMESTAMPWITHTIMEZONEvalueforthetimezoneregion'Australia/North'16-14Copyright©OracleCorporation,2001.Allrightsreserved.SELECTTO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')FROMDUAL;STRINGToTIMESTAMPConversionUsingTO_TIMESTAMPandTO_TIMESTAMP_TZSELECTTO_TIMESTAMP('2000-12-0111:00:00','YYYY-MM-DDHH:MI:SS')FROMDUAL;•Displaythecharacterstring'2000-12-0111:00:00'asaTIMESTAMPvalue.•Displaythecharacterstring'1999-12-0111:00:00-8:00'asaTIMESTAMPWITHTIMEZONEvalue.16-15Copyright©OracleCorporation,2001.Allrightsreserved.TimeIntervalConversionwithTO_YMINTERVALSELECThire_date,hire_date+TO_YMINTERVAL('01-02')ASHIRE_DATE_YMININTERVALFROMEMPLOYEESWHEREdepartment_id=20;•DisplayadatethatisoneyeartwomonthsafterthehiredatefortheemployeesworkinginthedepartmentwiththeDEPARTMENT_ID2016-16Copyright©OracleCorporation,2001.Allrightsreserved.Summary•TZ_OFFSET•FROM_TZ•TO_TIMESTAMP•TO_TIMESTAMP_TZ•TO_YMINTERVAL•CURRENT_DATE•CURRENT_TIMESTAMP•LOCALTIMESTAMP•DBTIMEZONE•SESSIONTIMEZONE•EXTRACTInthislesson,youshouldhavelearnedhowtousethefollowingfunctions:16-17Copyright©OracleCorporation,2001.Allrightsreserved.Practice16OverviewThispracticecoversusingtheOracle9idatetimefunctions.