Oracle时刻函数(转)ITeye - AG环亚娱乐

Oracle时刻函数(转)ITeye

2019年02月23日13时31分03秒 | 作者: 冰夏 | 标签: 时刻,函数,核算 | 浏览: 1014

常用的时刻格局 在oracle中有 yyyy-mm-dd hh24:mi:ss  而在Java中有些差异 为yyyy-MM-dd HH:mm:ss 这点仍是常常简略含糊的。

 

信任许多人都有过核算某些数据的阅历,比方,要核算财政的状况,或许要按每年,每季度,每月,乃至每个星期来别离核算。那在oracle中应该怎样来写sql句子呢,这个时分Oracle的日期函数会给咱们许多协助。

 常用日期型函数 
1。Sysdate 当时日期和时刻
SQL  Select sysdate from dual;

SYSDATE

21-6月 -05

2。Last_day 本月最终一天 
SQL  Select last_day(sysdate) from dual;

LAST_DAY(S

30-6月 -05

3。Add_months(d,n) 当时日期d后推n个月 
用于从一个日期值添加或削减一些月份 
date_value:=add_months(date_value,number_of_months)

SQL  Select add_months(sysdate,2) from dual;

ADD_MONTHS

21-8月 -05

4。Months_between(f,s) 日期f和s间相差月数 
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 

5。NEXT_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

6。current_date()回来当时会话时区中的当时日期 
date_value:=current_date 
SQL  column sessiontimezone for a15 
SQL  select sessiontimezone,current_date from dual; 

SESSIONTIMEZONE CURRENT_DA 
-  
+08:00          13-11月-03 
  
SQL  alter session set time_zone=-11:00 2  / 
会话已更改。 
  
SQL  select sessiontimezone,current_timestamp from dual; 

SESSIONTIMEZONE CURRENT_TIMESTAMP 
-  
-11:00          12-11月-03 04.59.13.668000 下午 -11:00 

7。current_timestamp()以timestamp with time zone数据类型回来当时会话时区中的当时日期
SQL  select current_timestamp from dual;

CURRENT_TIMESTAMP
-
21-6月 -05 10.13.08.220589 上午 +08:00

8。dbtimezone()回来时区
SQL  select dbtimezone from dual;

DBTIME

-08:00

9。extract()找出日期或距离值的字段值 
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

10。localtimestamp()回来会话中的日期和时刻 
SQL  select localtimestamp from dual;

LOCALTIMESTAMP
-
21-6月 -05 10.18.15.855652 上午

常用日期数据格局(该段为摘录)

Y或YY或YYY 年的最终一位,两位或三位 Select to_char(sysdate,’YYY’) from dual; 002表明2002年 
SYEAR或YEAR SYEAR使公元前的年份前加一负号 Select to_char(sysdate,’SYEAR’) from dual; -1112表明公元前111 2年 
Q 季度,1~3月为第一季度 Select to_char(sysdate,’Q’) from dual; 2表明第二季度① 
MM 月份数 Select to_char(sysdate,’MM’) from dual; 12表明12月 
RM 月份的罗马表明 Select to_char(sysdate,’RM’) from dual; IV表明4月 
Month 用9个字符长度表明的月份名 Select to_char(sysdate,’Month’) from dual; May后跟6个空格表明5月 
WW 当年第几周 Select to_char(sysdate,’WW’) from dual; 24表明2002年为第24周 
W 本月第几周 Select to_char(sysdate,’W’) from dual; 2002年为第1周 
DDD 当年第几, 为001,为032 Select to_char(sysdate,’DDD’) from dual; 363 2002年1 2月2 9日为第363天 
DD 当月第几天 Select to_char(sysdate,’DD’) from dual; 04 为第4天 
D 周内第几天 Select to_char(sysdate,’D’) from dual; 5 2002年为星期一 
DY 周内第几天缩写 Select to_char(sysdate,’DY’) from dual; SUN 2002年为星期天 
HH或HH12 12进制小时数 Select to_char(sysdate,’HH’) from dual; 02 午夜2点过8分为02 
HH24 24小时制 Select to_char(sysdate,’HH24’) from dual; 14 下午2点08分为14 
MI 分钟数(0~59) Select to_char(sysdate,’MI’) from dual; 17下午4点17分 
SS 秒数(0~59) Select to_char(sysdate,’SS’) from dual; 22 11点3分22秒 
提示留意不要将MM格局用于分钟(分钟应该运用MI)。MM是用于月份的格局,将它用于分钟也能作业,但成果是过错的。 


现在给出一些实践后的用法:

1。上月末天:
SQL  select to_char(add_months(last_day(sysdate),-1),yyyy-MM-dd) LastDay from
dual;

LASTDAY

2005-05-31

2。上月今日
SQL  select to_char(add_months(sysdate,-1),yyyy-MM-dd) PreToday from dual;


PRETODAY

2005-05-21

3.上月首天
SQL  select to_char(add_months(last_day(sysdate)+1,-2),yyyy-MM-dd) firstDay from dual;

FIRSTDAY

2005-05-01

4.依照每周进行核算
SQL  select to_char(sysdate,ww) from dual group by to_char(sysdate,ww);

TO

25

5。依照每月进行核算
SQL  select to_char(sysdate,mm) from 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,yyyy) from dual group by to_char(sysdate,yyyy);

TO_C

2005

8.要找到某月中所有周五的详细日期 
select to_char(t.d,YY-MM-DD) from ( 
select trunc(sysdate, MM)+rownum-1 as d 
from dba_objects 
where rownum   32) t 
where to_char(t.d, MM) = to_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, MM) = to_char(sysdate, MM)改成sysdate-90,即为查找当时月份的前三个月中的每周五的日期。

9.oracle中时刻运算

内容如下: 
1、oracle支撑对日期进行运算 
2、日期运算时是以天为单位进行的 
3、当需求以分秒等更小的单位算值时,按时刻进制进行转化即可 
4、进行时刻进制转化时留意加括号,不然会出问题 

SQL  alter session set nls_date_format=yyyy-mm-dd hh:mi:ss; 

会话已更改。 

SQL  set serverout on 
SQL  declare 
  2 DateValue date; 
  3 begin 
  4 select sysdate into DateValue from dual; 
  5 dbms_output.put_line(源时刻:||to_char(DateValue)); 
  6 dbms_output.put_line(源时刻减1天:||to_char(DateValue-1)); 
  7 dbms_output.put_line(源时刻减1天1小时:||to_char(DateValue-1-1/24)); 
  8 dbms_output.put_line(源时刻减1天1小时1分:||to_char(DateValue-1-1/24-1/(24*60))); 
  9 dbms_output.put_line(源时刻减1天1小时1分1秒:||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 
源时刻减1天1小时:2003-12-28 10:53:41 
源时刻减1天1小时1分:2003-12-28 10:52:41 
源时刻减1天1小时1分1秒:2003-12-28 10:52:40 

PL/SQL 进程已成功完结。


在Oracle中完成时刻相加处理
 称号:Add_Times
 功用:回来d1与NewTime相加今后的成果,完成时刻的相加
 阐明:关于NewTime中的日期不予考虑
 日期:2004-12-07
 版别:1.0
 作者:Kevin


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
/*
函数称号: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,所得即为作业日数目。      
-*/
  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,yyyymmdd) rq
                     FROM (SELECT substr(100+ROWNUM,2,2) dd 
                             FROM ljrq z 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
/*-
函数称号: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(即不是星期六和星期天),则算一个作业日。      
*/
  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;

以上两个版别的比较

第一个版别一条SQL句子就能够得出成果,不需求编程就能够到达意图。但需求运用恣意一张有权拜访的、记载条数至少为31的一张表或视图。
    第二个版别需求编程,但不需求表或许视图。
    这两个版别都还存在需求完善的当地,即没有考虑节日,如五一、十一、元旦、新年这些节假日都没有去除。这些节假日应该保护成一张表,然后经过查表来去除这些节假日。

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表AG环亚娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章