oracle sql日期比较:

第四部分:

相信很多人都有过统计某些数据的经历，比如，要统计财务的情况，可能要按每年，每季度，每月，甚至每个星期来分别统计。那在oracle中应该怎么来写sql语句呢，这个时候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
/*------------------------------------------------------------------------------------------
函数名称：Get_WorkingDays
中文名称：求某一年月中共有多少工作日
作者姓名:XINGPING
编写时间:2004-05-22
输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405
返回值：整型值，包含的工作日数目。
算法描述：
1).列举出参数给出的年月中的每一天。这里使用了一个表（ljrq是我的库中的一张表。这个表可以是有权访问的、记录条数至少为31的任意一张表或视图）来构造出某年月的每一天。
2).用这些日期和一个已知星期几的日期相减(2001-12-30是星期天)，所得的差再对7求模。如果所求年月在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模.
3).过滤掉结果集中值为0和6的元素，然后求count,所得即为工作日数目。
-------------------------------------------------------------------------------------------------*/
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
/*-----------------------------------------------------------------------------------------
函数名称：Get_WorkingDays
中文名称：求某一年月中共有多少工作日
作者姓名:XINGPING
编写时间:2004-05-23
输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405
返回值：整型值，包含的工作日数目。
算法描述：使用Last_day函数计算出参数所给年月共包含多少天，根据这个值来构造一个循环。在这个循环中先求这个月的每一天与一个已知是星期天的日期(2001-12-30是星期天)的差，所得的差再对7求模。如果所求日期在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模.如过所得值不等于0和6（即不是星期六和星期天），则算一个工作日。
----------------------------------------------------------------------------------------*/
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的一张表或视图。
第二个版本需要编程，但不需要表或者视图。
这两个版本都还存在需要完善的地方，即没有考虑节日，如五一、十一、元旦、春节这些节假期都没有去除。这些节假日应该维护成一张表，然后通过查表来去除这些节假日。