Oracle时间函数.docx
《Oracle时间函数.docx》由会员分享，可在线阅读，更多相关《Oracle时间函数.docx（27页珍藏版）》请在冰豆网上搜索。
Oracle时间函数
常用日期型函数
1。
Sysdate当前日期和时间
SQL>Selectsysdatefromdual;
SYSDATE
----------
21-6月-05
2。
Last_day本月最后一天
SQL>Selectlast_day（sysdate）fromdual;
LAST_DAY（S
----------
30-6月-05
3。
Add_months（d,n）当前日期d后推n个月
用于从一个日期值增加或减少一些月份
date_value:
=add_months（date_value,number_of_months）
SQL>Selectadd_months（sysdate,2）fromdual;
ADD_MONTHS
----------
21-8月-05
4。
Months_between（f,s）日期f和s间相差月数
SQL>selectmonths_between（sysdate,to_date（'2005-11-12','yyyy-mm-dd'））fromdual;
MONTHS_BETWEEN（SYSDATE,TO_DATE（'2005-11-12','YYYY-MM-DD'））
----------------------------------------------------------
-4.6966741
5。
NEXT_DAY（d,day_of_week）
返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。
参数"day_of_week"必须为该星期中的某一天。
SQL>SELECTnext_day（to_date（'20050620','YYYYMMDD'）,1）FROMdual;
NEXT_DAY（T
----------
26-6月-05
6。
current_date（）返回当前会话时区中的当前日期
date_value:
=current_date
SQL>columnsessiontimezonefora15
SQL>selectsessiontimezone,current_datefromdual;
SESSIONTIMEZONECURRENT_DA
-------------------------
+08:
0013-11月-03
SQL>altersessionsettime_zone='-11:
00'2/
会话已更改。
SQL>selectsessiontimezone,current_timestampfromdual;
SESSIONTIMEZONECURRENT_TIMESTAMP
---------------------------------------------------
-11:
0012-11月-0304.59.13.668000下午-11:
00
7。
current_timestamp（）以timestampwithtimezone数据类型返回当前会话时区中的当前日期
SQL>selectcurrent_timestampfromdual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-6月-0510.13.08.220589上午+08:
00
8。
dbtimezone（）返回时区
SQL>selectdbtimezonefromdual;
DBTIME
------
-08:
00
9。
extract（）找出日期或间隔值的字段值
date_value:
=extract（date_fieldfrom[datetime_value|interval_value]）
SQL>selectextract（monthfromsysdate）"ThisMonth"fromdual;
ThisMonth
----------
6
SQL>selectextract（yearfromadd_months（sysdate,36））"Years"fromdual;
Years
----------
2008
10。
localtimestamp（）返回会话中的日期和时间
SQL>selectlocaltimestampfromdual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
21-6月-0510.18.15.855652上午
常用日期数据格式（该段为摘抄）
Y或YY或YYY年的最后一位，两位或三位Selectto_char（sysdate,’YYY’）fromdual；002表示2002年
SYEAR或YEARSYEAR使公元前的年份前加一负号Selectto_char（sysdate,’SYEAR’）fromdual；-1112表示公元前1112年
Q季度，1～3月为第一季度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’）fromdual；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’）fromdual；SUN2002年3月24日为星期天
HH或HH1212进制小时数Selectto_char（sysdate,’HH’）fromdual；02午夜2点过8分为02
HH2424小时制Selectto_char（sysdate,’HH24’）fromdual；14下午2点08分为14
MI分钟数（0～59）Selectto_char（sysdate,’MI’）fromdual；17下午4点17分
SS秒数（0～59）Selectto_char（sysdate,’SS’）fromdual；2211点3分22秒
提示注意不要将MM格式用于分钟（分钟应该使用MI）。
MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。
现在给出一些实践后的用法：
1。
上月末天：
SQL>selectto_char（add_months（last_day（sysdate）,-1）,'yyyy-MM-dd'）LastDayfrom
dual;
LASTDAY
----------
2005-05-31
2。
上月今天
SQL>selectto_char（add_months（sysdate,-1）,'yyyy-MM-dd'）PreTodayfromdual;
PRETODAY
----------
2005-05-21
3.上月首天
SQL>selectto_char（add_months（last_day（sysdate）+1,-2）,'yyyy-MM-dd'）firstDayfromdual;
FIRSTDAY
----------
2005-05-01
4.按照每周进行统计
SQL>selectto_char（sysdate,'ww'）fromdualgroupbyto_char（sysdate,'ww'）;
TO
--
25
5。
按照每月进行统计
SQL>selectto_char（sysdate,'mm'）fromdualgroupbyto_char（sysdate,'mm'）;
TO
--
06
6。
按照每季度进行统计
SQL>selectto_char（sysdate,'q'）fromdualgroupbyto_char（sysdate,'q'）;
T
-
2
7。
按照每年进行统计
SQL>selectto_char（sysdate,'yyyy'）fromdualgroupbyto_char（sysdate,'yyyy'）;
TO_C
----
2005
8.要找到某月中所有周五的具体日期
selectto_char（t.d,'YY-MM-DD'）from（
selecttrunc（sysdate,'MM'）+rownum-1asd
fromdba_objects
whererownum<32）t
whereto_char（t.d,'MM'）=to_char（sysdate,'MM'）--找出当前月份的周五的日期
andtrim（to_char（t.d,'Day'））='星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把whereto_char（t.d,'MM'）=to_char（sysdate,'MM'）改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。
9.oracle中时间运算
内容如下：
1、oracle支持对日期进行运算
2、日期运算时是以天为单位进行的
3、当需要以分秒等更小的单位算值时，按时间进制进行转换即可
4、进行时间进制转换时注意加括号，否则会出问题
SQL>altersessionsetnls_date_format='yyyy-mm-ddhh:
mi:
ss';
会话已更改。
SQL>setserverouton
SQL>declare
2DateValuedate;
3begin
4selectsysdateintoDateValuefromdual;
5dbms_output.put_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）））;
10end;
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:
40
PL/SQL过程已成功完成。
在Oracle中实现时间相加处理
--名称：
Add_Times
--功能：
返回d1与NewTime相加以后的结果，实现时间的相加
--说明：
对于NewTime中的日期不予考虑
--日期：
2004-12-07
--版本：
1.0
--作者：
Kevin
createorreplacefunctionAdd_Times（d1indate,NewTimeindate）returndate
is
hhnumber;
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）+（ss/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”这样的表达式来计算这两个日期之间的时间差。
一旦你发现了时间差异，你可以使用简单的技巧来以天、小时、分钟或者秒为单位来计算时间差。
为了得到数据差，你必须选择合适的时间度量单位，这样就可以进行数据格式隐藏。
使用完善复杂的转换函数来转换日期是一个诱惑，但是你会发现这不是最好的解决方法。
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查询。
SQL>selectsysdate-（sysdate-3）fromdual;
SYSDATE-（SYSDATE-3）
-------------------
3
这里，我们看到了Oracle使用天来作为消逝时间的单位，所以我们可以很容易的使用转换函数来把它转换成小时或者分钟。
然而，当分钟数不是一个整数时，我们就会遇到放置小数点的问题。
Select
（sysdate-（sysdate-3.111））*1440
from
dual;
（SYSDATE-（SYSDATE-3.111））*1440
------------------------------
4479.83333
当然，我们可以用ROUND函数（即取整函数）来解决这个问题，但是要记住我们必须首先把DATE数据类型转换成NUMBER数据类型。
Select
round（to_number（sysdate-（sysdate-3.111））*1440）
from
dual;
ROUND（TO_NUMBER（SYSDATE-（SYSDATE-3.111））*1440）
----------------------------------------------
4480
我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入Oracle表格中。
在这个例子里，我们有一个离线（logoff）系统级触发机制来计算已经开始的会话时间并把它放入一个OracleSTATSPACKUSER_LOG扩展表格之中。
Update
perfstat.stats$user_log
set
elapsed_minutes=
round（to_number（logoff_time-logon_time）*1440）
where
user=user_id
and
elapsed_minutesisNULL;
查出任一年月所含的工作日
CREATEORREPLACEFUNCTIONGet_WorkingDays（
nyINVARCHAR2
）RETURNINTEGERIS
ResultINTEGER;
BEGIN
SELECTCOUNT（*）INTOResult
FROM（SELECTMOD（MOD（q.rq-to_date（'2001-12-30','yyyy-mm-dd'）,7）,7）weekday
FROM（SELECTto_date（ny||t.dd,'yyyymmdd'）rq
FROM（SELECTsubstr（100+ROWNUM,2,2）dd
FROMljrqzWHERERownum<=31
）t
WHEREto_date（ny||t.dd,'yyyymmdd'）
BETWEENto_date（ny,'yyyymm'）
ANDlast_day（to_date（ny,'yyyymm'））
）q
）a
WHEREa.weekdayNOTIN（0,6）;
RETURNResult;
ENDGet_WorkingDays;
______________________________________
还有一个版本
CREATEORREPLACEFUNCTIONGet_WorkingDays（
nyINVARCHAR2
）RETURNINTEGERIS
ResultINTEGER:
=0;
mytsINTEGER;--所给年月的天数
sctsINTEGER;--某天距2001-12-30所差的天数
rqDATE;
djtINTEGER:
=1;--
BEGIN
myts:
=to_char（last_day（to_date（ny,'yyyymm'））,'dd'）;
LOOP
rq:
=TO_date（ny||substr（100+djt,2）,'yyyymmdd'）;
scts:
=rq-to_date（'2001-12-30','yyyy-mm-dd'）;
IFMOD（MOD（scts,7）+7,7）NOTIN（0,6）THEN
Result:
=Result+1;
ENDIF;
djt:
=djt+1;
EXITWHENdjt>myts;
ENDLOOP;
RETURNResult;
ENDGet_WorkingDays;
以上两个版本的比较
第一个版本一条SQL语句就可以得出结果，不需要编程就可以达到目的。
但需要使用任意一张有权访问的、记录条数至少为31的一张表或视图。
第二个版本需要编程，但不需要表或者视图。
这两个版本都还存在需要完善的地方，即没有考虑节日，如五一、十一、元旦、春节这些节假期都没有去除。
这些节假日应该维护成一张表，然后通过查表来去除这些节假日。
oracle时间函数
1、转换函数
与date操作关系最大的就是两个转换函数：
to_date（）,to_char（）
to_date（）作用将字符类型按一定格式转化为日期类型：
具体用法:
to_date（'2004-11-27','yyyy-mm-dd'）,前者为字符串，后者为转换日期格式，注意，前后两者要一一对应。
如;to_date（'2004-11-2713:
34:
43','yyyy-mm-ddhh24:
mi:
ss'）将得到具体的时间
多种日期格式：
YYYY：
四位表示的年份
YYY，YY，Y：
年份的最后三位、两位或一位，缺省为当前世纪
MM：
01~12的月份编号
MONTH：
九个字符表示的月份，右边用空格填补
MON：
三位字符的月份缩写
WW：
一年中的星期
D：
星期中的第几天
DD：
月份中的第几天
DDD：
年所中的第几天
DAY：
九个字符表示的天的全称，右边用空格补齐
HH，HH12：
一天中的第几个小时，12进制表示法
HH24：
一天中的第几个小时，取值为00~23
MI：
一小时中的分钟
SS：
一分钟中的秒
SSSS：
从午夜开始过去的秒数
to_char（）:
将日期转按一定格式换成字符类型
SQL>selectto_char（sysdate,'yyyy-mm-ddhh24:
mi:
ss'）timefromdual;
TIME
-------------------
2004-10-0815:
22:
58
即把当前时间按yyyy-mm-ddhh24:
mi:
ss格式转换成字符类型
在oracle中处理日期大全
TO_DATE格式
Day:
ddnumber12
dyabbreviatedfri
dayspelledoutfriday
ddspthspelledout,ordinaltwelfth
Month:
mmnumber03
mon
升级会员 