ITPub博客

首页 > 数据库 > Oracle > PLSQL里常用的时间处理函数

PLSQL里常用的时间处理函数

Oracle 作者:jack2000_lu 时间:2013-11-20 08:37:39 0 删除 编辑

PLSQL里常用的时间处理函数

常用日期型函数

1Sysdate 当前日期和时间
SQL> Select sysdate from dual;
SYSDATE
———-
21-6月 -05
2Last_day 本月最后一天
SQL> Select last_day(sysdate) from dual;
LAST_DAY(S
———-
30-6月 -05
3Add_months(d,n) 当前日期d后推n个月
用于从一个日期值增加或减少一些月份
SQL> Select add_months(sysdate,2) from dual;
ADD_MONTHS
———-
21-8月 -05
4Months_between(f,s) 日期fs间相差月数
SQL> select months_between(sysdate,to_date(2005-11-12,yyyy-mm-dd))from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE(2005-11-12,YYYY-MM-DD))
———————————————————-
-4.6966741
5NEXT_DAY(d, day_of_week)
返回由”day_of_week”命名的,在变量”d”指定的日期之后的第一个工作日的日期。参数”day_of_week”必须为该星期中的某一天。
SQL> SELECT next_day(to_date(20050620,YYYYMMDD),1) FROM dual;
NEXT_DAY(T
———-
26-6月 -05
6current_date()返回当前会话时区中的当前日期
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
SESSIONTIMEZONE CURRENT_DA
————— ———-
+08:00 13-11-03
7current_timestamp()timestamp with time zone数据类型返回当前会话时区中的当前日期
SQL> alter session set time_zone=-11:00′ /
会话已更改。
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
————— ————————————
-11:00 12-11-03 04.59.13.668000 下午 -11:00
8dbtimezone()返回时区
SQL> select dbtimezone from dual;
DBTIME
——
-08:00
9extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) This Month” from dual;
This Month
———-
6
SQL> select extract(year from add_months(sysdate,36)) ” Years” from dual;
Years
———-
2008
10localtimestamp()返回会话中的日期和时间
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
—————————————————————————
21-6月 -05 10.18.15.855652 上午
常用日期数据格式
YYYYYY 年的最后一位,两位或三位 Select to_char(sysdate,YYYfrom dual; 002表示2002
SYEARYEAR SYEAR使公元前的年份前加一负号 Select to_char(sysdate,SYEARfrom dual; -1112表示公元前1112
季度,13月为第一季度 Select to_char(sysdate,Qfrom dual; 2表示第二季度①
MM 月份数 Select to_char(sysdate,MMfrom dual; 12表示12
RM 月份的罗马表示 Select to_char(sysdate,RMfrom dual; IV表示4
Month 9个字符长度表示的月份名 Select to_char(sysdate,Monthfrom dual; May后跟6个空格表示5
WW 当年第几周 Select to_char(sysdate,WWfrom dual; 24表示2002613日为第24
本月第几周 Select to_char(sysdate,Wfrom dual; 2002101日为第1
DDD 当年第几11日为00121日为032 Select to_char(sysdate,DDDfrom dual; 363 200229日为第363
DD 当月第几天 Select to_char(sysdate,DDfrom dual; 04 104日为第4
周内第几天 Select to_char(sysdate,Dfrom dual; 2002314日为星期一
DY 周内第几天缩写 Select to_char(sysdate,DYfrom dual; SUN 2002324日为星期天
HHHH12 12进制小时数 Select to_char(sysdate,HHfrom dual; 02 午夜2点过8分为02
HH24 24小时制 Select to_char(sysdate,HH24from dual; 14 下午208分为14
MI 分钟数(059) Select to_char(sysdate,MIfrom dual; 17下午417
SS 秒数(059) Select to_char(sysdate,SSfrom dual; 22 11322
提示注意不要将MM格式用于分钟(分钟应该使用MI)MM是用于月份的格式,将它用于分钟也能工作,但结果是错误的。
现在给出一些其他用法:
1、上月末天:
SQL> select to_char(add_months(last_day(sysdate),-1),yyyy-MM-ddLastDay from dual;
LASTDAY
———-
2005-05-31
2、上月今天
SQL> select to_char(add_months(sysdate,-1),yyyy-MM-ddPreToday from dual;
PRETODAY
———-
2005-05-21
3、上月首天
SQL> select to_char(add_months(last_day(sysdate)+1,-2),yyyy-MM-ddfirstDay from dual;
FIRSTDAY
———-
2005-05-01
4、按照每周进行统计
SQL> select to_char(sysdate,wwfrom dual group by to_char(sysdate,ww);
TO

25
5、按照每月进行统计
SQL> select to_char(sysdate,mmfrom dual group by to_char(sysdate,mm);
TO

06
6、按照每季度进行统计
SQL> select to_char(sysdate,q') from dual group by to_char(sysdate,q');
T
-
2
7、按照每年进行统计
SQL> select to_char(sysdate,yyyyfrom dual group by to_char(sysdate,yyyy);
TO_C
-
2005
8、要找到某月中所有周五的具体日期
select to_char(t.d,YY-MM-DDfrom (
select trunc(sysdate, MM)+rownum-1 as d
from dba_objects
where rownum 32) t
where to_char(t.d, MMto_char(sysdate, MM–找出当前月份的周五的日期
and trim(to_char(t.d, Day)) ‘星期五’
——–
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(t.d, MMto_char(sysdate, MM)改成sysdate-90,即为查找当前月份的前三个月中的每周五的日期。
9、确定某年某月有多少天
实现原理:先利用DATEDIFF取得当前月的第一天,再将月份加一取得下月第一天,然后减去1分钟,再取日期的天数部分,即为当月最大日期,也即当月天数
CREATE FUNCTION DaysInMonth @date datetime Returns int
AS
BEGIN
RETURN Day(dateadd(mi,-3,DATEADD(m, DATEDIFF(m,0,@date)+1,0)))
END
调用示例:
select dbo.DaysInMonth (2006-02-03)
10、计算哪一天是本周的星期一
SELECT DATEADD(week, DATEDIFF(week,1900-01-01,getdate()), 1900-01-01–返回2006-11-06 00:00:00.000
SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)
11、当前季度的第一天
SELECT DATEADD(quarter, DATEDIFF(quarter,0,getdate()), 0)—返回2006-10-01 00:00:00.000
12、一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between and then 1
when @m between and then 4
when @m between and then 7
else 10 end
select @time=datename(year,getdate())+-'+convert(varchar(10),@m)+-01
select datediff(day,@time,dateadd(mm,3,@time)) —返回92

常用时间运算:
1oracle支持对日期进行运算
2、日期运算时是以天为单位进行的
3、当需要以分秒等更小的单位算值时,按时间进制进行转换即可
4、进行时间进制转换时注意加括号,否则会出问题
SQL> alter session set nls_date_format=yyyy-mm-dd hh:mi:ss;
会话已更改。
SQL> set serverout on
SQL> declare
DateValue date;
begin
select sysdate into DateValue from dual;
dbms_output.put_line(‘源时间:||to_char(DateValue));
dbms_output.put_line(‘源时间减1:||to_char(DateValue-1));
dbms_output.put_line(‘源时间减11小时:||to_char(DateValue-1-1/24));
dbms_output.put_line(‘源时间减11小时1:||to_char(DateValue-1-1/24-1/(24*60)));
dbms_output.put_line(‘源时间减11小时11:||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));
10 end;
11 /
源时间:2003-12-29 11:53:41
源时间减1:2003-12-28 11:53:41
源时间减11小时:2003-12-28 10:53:41
源时间减11小时1:2003-12-28 10:52:41
源时间减11小时11:2003-12-28 10:52:40
PL/SQL 过程已成功完成。
Oracle中实现时间相加处理
– 名称:Add_Times
– 功能:返回d1NewTime相加以后的结果,实现时间的相加
– 说明:对于NewTime中的日期不予考虑
create or replace function Add_Times(d1 in date,NewTime in date) return date
is
hh number;
mm number;
ss number;
hours number;
dResult date;
begin
– 下面依次取出时、分、秒
select to_number(to_char(NewTime,HH24)) into hh from dual;
select to_number(to_char(NewTime,MI)) into mm from dual;
select to_number(to_char(NewTime,SS)) into ss from dual;
– 换算出NewTime中小时总和,在一天的百分几
hours := (hh (mm 60) (ss 3600))/ 24;
– 得出时间相加后的结果
select d1 hours into dResult from dual;
return(dResult);
end Add_Times;
– 测试用例
– select Add_Times(sysdate,to_date(2004-12-06 03:23:00,YYYY-MM-DD HH24:MI:SS)) from dual

Oracle9i中计算时间差
计算时间差是Oracle DATA数据类型的一个常见问题。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> select sysdate-(sysdate-3) from dual;
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)系统级触发机制来计算已经开始的会话时间并把它放入一个Oracle STATSPACK USER_LOG扩展表格之中。
Update perfstat.stats$user_log set
elapsed_minutes round(to_number(logoff_time-logon_time)*1440)
where user user_id and elapsed_minutes is NULL;
查出任一年月所含的工作日
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
RETURN INTEGER IS

Result INTEGER;
BEGIN
SELECT COUNT(*) INTO Result
FROM (SELECT MOD(MOD(q.rq-to_date(2001-12-30,yyyy-mm-dd),7),7) weekday
FROM SELECT to_date(ny||t.dd,yyyymmddrq
FROM (SELECT substr(100+ROWNUM,2,2) dd
FROM ljrq WHERE Rownum<=31
t
WHERE to_date(ny||t.dd,yyyymmdd)
BETWEEN to_date(ny,yyyymm)
AND last_day(to_date(ny,yyyymm))
)q
a
WHERE a.weekday NOT IN(0,6);
RETURN Result;
END Get_WorkingDays;
___________________________________
还有一个版本
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
RETURN INTEGER IS

Result INTEGER := 0;
myts INTEGER; –所给年月的天数
scts INTEGER; –某天距2001-12-30所差的天数
rq DATE;
djt INTEGER := 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);
IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN
Result := Result 1;
END IF;
djt := djt 1;
EXIT WHEN djt>myts;
END LOOP;
RETURN Result;
END Get_WorkingDays;

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26734/viewspace-1115687/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-01-31