《Oracle时间操作参考.doc》由会员分享，可在线阅读，更多相关《Oracle时间操作参考.doc（11页珍藏版）》请在三一文库上搜索。
1、相信很多人都有过统计某些数据的经历，比如，要统计财务的情况，可能要按每年，每季度，每月，甚至每个星期来分别统计。那在oracle中应该怎么来写sql语句呢，这个时候Oracle的日期函数会给我们很多帮助。select count(live_id ) from sa.lg_tbl_citizen t where t.L_LAST_DATE = TO_DATE( 2009-09-01 00:00:00, YYYY-MM-DD HH24:MI:SS)and t.L_LAST_DATE Selectsysdatefromdual;SYSDATE-21-6月-052。Last_day本月最后一天SQLS
2、electlast_day(sysdate)fromdual;LAST_DAY(S-30-6月-053。Add_months(d,n)当前日期d后推n个月用于从一个日期值增加或减少一些月份date_value:=add_months(date_value,number_of_months)SQLSelectadd_months(sysdate,2)fromdual;ADD_MONTHS-21-8月-054。Months_between(f,s)日期f和s间相差月数SQLselectmonths_between(sysdate,to_date(2005-11-12,yyyy-mm-dd)from
3、dual;MONTHS_BETWEEN(SYSDATE,TO_DATE(2005-11-12,YYYY-MM-DD)-4.69667415。NEXT_DAY(d,day_of_week)返回由day_of_week命名的，在变量d指定的日期之后的第一个工作日的日期。参数day_of_week必须为该星期中的某一天。SQLSELECTnext_day(to_date(20050620,YYYYMMDD),1)FROMdual;NEXT_DAY(T-26-6月-056。current_date()返回当前会话时区中的当前日期date_value:=current_dateSQLcolumnsess
4、iontimezonefora15SQLselectsessiontimezone,current_datefromdual;SESSIONTIMEZONECURRENT_DA-+08:0013-11月-03SQLaltersessionsettime_zone=-11:002/会话已更改。SQLselectsessiontimezone,current_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP-11:0012-11月-0304.59.13.668000下午-11:007。current_timestamp()以timestampwi
5、thtimezone数据类型返回当前会话时区中的当前日期SQLselectcurrent_timestampfromdual;CURRENT_TIMESTAMP-21-6月-0510.13.08.220589上午+08:008。dbtimezone()返回时区SQLselectdbtimezonefromdual;DBTIME-08:009。extract()找出日期或间隔值的字段值date_value:=extract(date_fieldfromdatetime_value|interval_value)SQLselectextract(monthfromsysdate)ThisMonth
6、fromdual;ThisMonth-6SQLselectextract(yearfromadd_months(sysdate,36)Yearsfromdual;Years-200810。localtimestamp()返回会话中的日期和时间SQLselectlocaltimestampfromdual;LOCALTIMESTAMP-21-6月-0510.18.15.855652上午常用日期数据格式（该段为摘抄）Y或YY或YYY年的最后一位，两位或三位Selectto_char(sysdate,YYY)fromdual；002表示2002年SYEAR或YEARSYEAR使公元前的年份前加一负号
7、Selectto_char(sysdate,SYEAR)fromdual；-1112表示公元前1112年Q季度，13月为第一季度Selectto_char(sysdate,Q)fromdual；2表示第二季度MM月份数Selectto_char(sysdate,MM)fromdual；12表示12月RM月份的罗马表示Selectto_char(sysdate,RM)fromdual；IV表示4月Month用9个字符长度表示的月份名Selectto_char(sysdate,Month)fromdual；May后跟6个空格表示5月WW当年第几周Selectto_char(sysdate,WW)f
8、romdual；24表示2002年6月13日为第24周W本月第几周Selectto_char(sysdate,W)fromdual；2002年10月1日为第1周DDD当年第几,1月1日为001，2月1日为032Selectto_char(sysdate,DDD)fromdual；3632002年12月29日为第363天DD当月第几天Selectto_char(sysdate,DD)fromdual；0410月4日为第4天D周内第几天Selectto_char(sysdate,D)fromdual；52002年3月14日为星期一DY周内第几天缩写Selectto_char(sysdate,DY)
9、fromdual；SUN2002年3月24日为星期天HH或HH1212进制小时数Selectto_char(sysdate,HH)fromdual；02午夜2点过8分为02HH2424小时制Selectto_char(sysdate,HH24)fromdual；14下午2点08分为14MI分钟数(059)Selectto_char(sysdate,MI)fromdual；17下午4点17分SS秒数(059)Selectto_char(sysdate,SS)fromdual；2211点3分22秒提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果
10、是错误的。现在给出一些实践后的用法：1。上月末天：SQLselectto_char(add_months(last_day(sysdate),-1),yyyy-MM-dd)LastDayfromdual;LASTDAY-2005-05-312。上月今天SQLselectto_char(add_months(sysdate,-1),yyyy-MM-dd)PreTodayfromdual;divTODAY-2005-05-213.上月首天SQLselectto_char(add_months(last_day(sysdate)+1,-2),yyyy-MM-dd)firstDayfromdual;F
11、IRSTDAY-2005-05-014.按照每周进行统计SQLselectto_char(sysdate,ww)fromdualgroupbyto_char(sysdate,ww);TO-255。按照每月进行统计SQLselectto_char(sysdate,mm)fromdualgroupbyto_char(sysdate,mm);TO-066。按照每季度进行统计SQLselectto_char(sysdate,q)fromdualgroupbyto_char(sysdate,q);T-27。按照每年进行统计SQLselectto_char(sysdate,yyyy)fromdualgro
12、upbyto_char(sysdate,yyyy);TO_C-20058.要找到某月中所有周五的具体日期selectto_char(t.d,YY-MM-DD)from(selecttrunc(sysdate,MM)+rownum-1asdfromdba_objectswhererownumaltersessionsetnls_date_format=yyyy-mm-ddhh:mi:ss;会话已更改。SQLsetserveroutonSQLdeclare2DateValuedate;3begin4selectsysdateintoDateValuefromdual;5dbms_output.pu
13、t_line(源时间:|to_char(DateValue);6dbms_output.put_line(源时间减1天:|to_char(DateValue-1);7dbms_output.put_line(源时间减1天1小时:|to_char(DateValue-1-1/24);8dbms_output.put_line(源时间减1天1小时1分:|to_char(DateValue-1-1/24-1/(24*60);9dbms_output.put_line(源时间减1天1小时1分1秒:|to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60);10en
14、d;11/源时间:2003-12-2911:53:41源时间减1天:2003-12-2811:53:41源时间减1天1小时:2003-12-2810:53:41源时间减1天1小时1分:2003-12-2810:52:41源时间减1天1小时1分1秒:2003-12-2810:52:40PL/SQL过程已成功完成。在Oracle中实现时间相加处理-名称：Add_Times-功能：返回d1与NewTime相加以后的结果，实现时间的相加-说明：对于NewTime中的日期不予考虑-日期：2004-12-07-版本：1.0-作者：KevincreateorreplacefunctionAdd_Times(
15、d1indate,NewTimeindate)returndateishhnumber;mmnumber;ssnumber;hoursnumber;dResultdate;begin-下面依次取出时、分、秒selectto_number(to_char(NewTime,HH24)intohhfromdual;selectto_number(to_char(NewTime,MI)intommfromdual;selectto_number(to_char(NewTime,SS)intossfromdual;-换算出NewTime中小时总和，在一天的百分几hours:=(hh+(mm/60)+(s
16、s/3600)/24;-得出时间相加后的结果selectd1+hoursintodResultfromdual;return(dResult);endAdd_Times;-测试用例-selectAdd_Times(sysdate,to_date(2004-12-0603:23:00,YYYY-MM-DDHH24:MI:SS)fromdual在Oracle9i中计算时间差计算时间差是OracleDATA数据类型的一个常见问题。Oracle支持日期计算，你可以创建诸如“日期1日期2”这样的表达式来计算这两个日期之间的时间差。一旦你发现了时间差异，你可以使用简单的技巧来以天、小时、分钟或者秒为单位来
17、计算时间差。为了得到数据差，你必须选择合适的时间度量单位，这样就可以进行数据格式隐藏。使用完善复杂的转换函数来转换日期是一个诱惑，但是你会发现这不是最好的解决方法。round(to_number(end-date-start_date)-消逝的时间（以天为单位）round(to_number(end-date-start_date)*24)-消逝的时间（以小时为单位）round(to_number(end-date-start_date)*1440)-消逝的时间（以分钟为单位）显示时间差的默认模式是什么？为了找到这个问题的答案，让我们进行一个简单的SQL*Plus查询。SQLselectsys
18、date-(sysdate-3)fromdual;SYSDATE-(SYSDATE-3)-3这里，我们看到了Oracle使用天来作为消逝时间的单位，所以我们可以很容易的使用转换函数来把它转换成小时或者分钟。然而，当分钟数不是一个整数时，我们就会遇到放置小数点的问题。Select(sysdate-(sysdate-3.111)*1440fromdual;(SYSDATE-(SYSDATE-3.111)*1440-4479.83333当然，我们可以用ROUND函数（即取整函数）来解决这个问题，但是要记住我们必须首先把DATE数据类型转换成NUMBER数据类型。Selectround(to_numb
19、er(sysdate-(sysdate-3.111)*1440)fromdual;ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111)*1440)-4480我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入Oracle表格中。在这个例子里，我们有一个离线（logoff）系统级触发机制来计算已经开始的会话时间并把它放入一个OracleSTATSPACKUSER_LOG扩展表格之中。Updateperfstat.stats$user_logsetelapsed_minutes=round(to_number(logoff_time-logon_time)*144
20、0)whereuser=user_idandelapsed_minutesisNULL;查出任一年月所含的工作日CREATEORREPLACEFUNCTIONGet_WorkingDays(nyINVARCHAR2)RETURNINTEGERIS/*-函数名称：Get_WorkingDays中文名称：求某一年月中共有多少工作日作者姓名:XINGPING编写时间:2004-05-22输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405返回值：整型值，包含的工作日数目。算法描述：1).列举出参数给出的年月中的每一天。这里使用了一个表（ljrq是我的库中的一张表。这个表可以是有
21、权访问的、记录条数至少为31的任意一张表或视图）来构造出某年月的每一天。2).用这些日期和一个已知星期几的日期相减(2001-12-30是星期天)，所得的差再对7求模。如果所求年月在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模.3).过滤掉结果集中值为0和6的元素，然后求count,所得即为工作日数目。-*/ResultINTEGER;BEGINSELECTCOUNT(*)INTOResultFROM(SELECTMOD(MOD(q.rq-to_date(2001-12-30,yyyy-mm-dd),7)
22、,7)weekdayFROM(SELECTto_date(ny|t.dd,yyyymmdd)rqFROM(SELECTsubstr(100+ROWNUM,2,2)ddFROMljrqzWHERERownummyts;ENDLOOP;RETURNResult;ENDGet_WorkingDays;以上两个版本的比较第一个版本一条SQL语句就可以得出结果，不需要编程就可以达到目的。但需要使用任意一张有权访问的、记录条数至少为31的一张表或视图。第二个版本需要编程，但不需要表或者视图。这两个版本都还存在需要完善的地方，即没有考虑节日，如五一、十一、元旦、春节这些节假期都没有去除。这些节假日应该维护成一张表，然后通过查表来去除这些节假日。