搜索
Oracle9i中与时间有关的学习小结一、Oracle9i中的时区设置和修改1、时区的设置当数据库创建时通过createdatabase...settime_zone='AMERICA/NEW_YORK'..;或者通过alterdatabasesettime_zone='AMERICA/NEW_YORK';设置以后,DBTIMEZONE就可以返回当前数据库的时区设置信息.如果在创建数据库时没有指定time_zone的设置,那么就使用和当前操作系统一致的时区.有效的时区名称和信息,可以通过视图v$timezone_names来查看.TZNAME字段对应时区的名称.2、时区的调整以一个实际案例来说明。SQLselectdbtimezonefromdual;DBTIME------0:00当时，我们查时间：SQLselectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;2005-01-1503:15:33其时，操作时间是2005-01-1511:15:33，相差是8个小时。数据库时区和操作系统不是一个时区。该怎么解决呢？摸着石头过河吧！通过查询相关文档，得知可以使用alterdatabasesettime_zone语句修改。于是，我就以sys用户登陆数据库，然后发出命令：SQLalterdatabasesettime_zone='+8:00';alterdatabasesettime_zone='+8:00'*第1行出现错误:ORA-02231:缺少或无效的ALTERDATABASE选项注：问题是数据库中有一些列的数据类型是：TIMESTAMPWITHLOCALTIMEZONE只要把这些列删除了就可以了。metalink上的230099.1提供了一个脚本，查找哪些列的数据类型是TIMESTAMPWITHLOCALTIMEZONE类型。执行该脚本：SQLselectu.name||'.'||o.name||'.'||c.nameTSLTZcolumnfromsys.obj$o,sys.col$c,sys.user$uwherec.type#=231ando.obj#=c.obj#andu.user#=o.owner#;TSLTZCOLUMN------------------------------------------------------------------------OE.ORDERS.ORDER_DATE发现oe用户下orders表中的字段order_dateTIMESTAMPWITHLOCALTIMEZONE类型的：SQLdescoe.orders名称是否为空?类型-----------------------------------------------------------------------------ORDER_IDNOTNULLNUMBER(12)ORDER_DATENOTNULLTIMESTAMP(6)WITHLOCALTIMEZONEORDER_MODEVARCHAR2(8)CUSTOMER_IDNOTNULLNUMBER(6)ORDER_STATUSNUMBER(2)ORDER_TOTALNUMBER(8,2)SALES_REP_IDNUMBER(6)PROMOTION_IDNUMBER(6)该表是一个无关紧要的表，于是删除该中order_date字段。SQLaltertableoe.ordersdropcolumnorder_date;表已更改。然后尝试更改数据库时区：SQLalterdatabasesettime_zone='+8:00';数据库已更改。接着，关闭并重新启动数据库查询时区信息SQLselectdbtimezonefromdual;DBTIME------+08:00至此，我们已经更改数据库时区成功！二、Oracle9i中的缺省日期格式DD-MON-RR在ORACLE9I之前,日期格式的数据类型默认格式为“DD-MON-YY”，而在ORACLE9I中变为“DD-MON-RR”。那么这个“RR”究竟代表什么意思呢？查阅了相关资料发现，原来Oracle为了解决千年问题，而引入了RR日期型格式。1、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后两位数也为0—49，则返回的日期在本世纪。例如：当前年份为2005年，指明的日期是01-OCT-08，则RR日期格式返回的日期为：2008年10月1日。而YY日期格式返回的日期也为：2008年10月1日2、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后两位数为50—99，则返回的日期在上一世纪。例如：当前年份为2005年，指明的日期是01-OCT-98，RR日期格式返回的日期为：1998年10月1日。而YY日期格式返回的日期为：2098年10月1日。这也许就是我们所说的两千年问题。3、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后两位数为0—49，则返回的日期在下一世纪。例如：当前年份为1999年，指明的日期是01-OCT-08，RR日期格式返回的日期为：2008年10月1日。而YY日期格式返回的日期为：1908年10月1日。4、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后两位数也为50—99，则返回的日期在本世纪。例如：当前年份为1999年，指明的日期是01-OCT-98，RR日期格式返回的日期为：1998年10月1日。而YY日期格式返回的日期也为：1998年10月1日。ExamplesTofindemployeeshiredpriorto1990,usetheRRformat,whichproducesthesameresultswhetherthecommandisrunin1999ornow。三、Oracle9i中的常用date函数SQLselectlast_name,to_char(hire_date,'DD-Mon-YYYY')fromemployeeswherehire_dateto_date('01-Jan-90','DD-Mon-RR');●Oracledatabasestoresdatesinaninternalnumericformat:century,year,month,day,hours,minutes,seconds.●日期型字段的插入和更新可以数据型或字符并带to_date函数说明即可。●缺省的日期格式有NLS_DATE_FORMAT参数控制，它的缺省格式为DD-MON-RR。Allowsyoutostore21stcenturydatesinthe20thcenturybyspecifyingonlythelasttwodigitsoftheyear.Allowsyoutostore20thcenturydatesinthe21stcenturyinthesameway.●缺省的时间是夜里00:00:00(即0点0分0秒)。●sysdate返回的是服务器的时间。●日期格式的显示可以设置。●日期型可以运算。●世纪用cc表示；年用yyyy表示，月用mm表示，日用dd表示，小时用hh24表示，分用mi表示，秒用ss表示。Oracle中常用的日期操作函数如下：函数名称功能months_between返回两个日期之间的月份数add_months返回给定日期增加/减少指定月份后得到的日期next_day返回指定日期后的星期对应的日期last_day返回当前日期对应的本月中的最后一天的日期roundRoundDatetruncTruncDate下面分别示例说明1）months_between(date1,date2)returnsnumberofmonthsbetweendatesdate1anddate2.Ifdate1islaterthandate2,thentheresultispositive.Ifdate1isearlierthandate2,thentheresultisnegative.Ifdate1anddate2areeitherthesamedaysofthemonthorbothlastdaysofmonths,thentheresultisalwaysaninteger.OtherwiseOraclecalculatesthefractionalportionoftheresultbasedona31-daymonthandconsidersthedifferenceintimecomponentsdate1anddate2.Examples2）add_months(date,n)returnsthedatedplusnmonths.Theargumentncanbeanyinteger.Ifdisthelastdayofthemonthoriftheresultingmonthhasfewerdaysthanthedaycomponentofd,thentheresultisthelastdayoftheresultingmonth.Otherwise,theresulthasthesamedaycomponentasd.ExamplesSQLselectsysdatefromdual;20050423231955SQLselectadd_months(sysdate,1)fromdual;20050523232004SQLselectadd_months(sysdate,-1)fromdual;20050323232016SQL给出date2-date1的月份SQLselectmonths_between('19-12月-1999','19-3月-1999')mon_betweenfromdual;MON_BETWEEN-----------9SQLselectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd'))mon_betwfromdual;MON_BETW----------60SQL3）next_day(date,char)returnsthedateofthefirstweekdaynamedbycharthatislaterthanthedatedate.Theargumentcharmustbeadayoftheweekinthedatelanguageofyoursession,eitherthefullnameortheabbreviation.Theminimumnumberoflettersrequiredisthenumberoflettersintheabbreviatedversion.Anycharactersimmediatelyfollowingthevalidabbreviationareignored.Thereturnvaluehasthesamehours,minutes,andsecondscomponentastheargumentdate.Examples4）last_day(date)returnsthedateofthelastdayofthemonththatcontainsdateSQLselectsysdatefromdual;20050423231955SQLselectnext_day(sysdate,'星期一')fromdual;20050425230910SQLselectnext_day(sysdate,'星期六')fromdual;20050430231000SQLaltersessionsetnls_language=american;Sessionaltered.SQLselectsysd