ORACLE 日常笔记 原创

常用oracle函数

1、日期转秒

1 CREATE OR REPLACE FUNCTION "DATE_TO_NUM" (
2  xin in date
3 )
4 return number
5 as
6 begin
7  return  (xin - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60;
8 end;

eg. SELECT date_to_num(sysdate) FROM dual ;

2、秒转日期

1 CREATE OR REPLACE FUNCTION "NUM_TO_DATE"  (xin in number)
2     return date is
3  out_para date;
4  begin
5  select (xin+28800)/3600/24+to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') into out_para from dual;
6  return out_para;
7  end;

eg. SELECT num_to_date(1346727589) FROM DUAL ;

3、查询两个时间点之差 多次求和并去重

 1 CREATE OR REPLACE FUNCTION REMOVE_REPEAT_TIME_SECONDS(
 2        STARTTIMENAME IN VARCHAR2,        --开始时间字段名
 3        ENDTIMENANAME IN VARCHAR2,        --结束时间字段名
 4        CONDITION     IN VARCHAR2,        --条件，格式为："FROM表名WHERE条件名"
 5        DURATIONTYPE  IN VARCHAR2)        --时长类型：'nature'为自然时长
 6     RETURN NUMBER IS                     --返回数字类型(单位为天)
 7  TOTALTIME NUMBER;          --总时长
 8  STARTTIME DATE;            --开始时间
 9  ENDTIME DATE;              --结束时间
10  FIRSTTIME DATE;            --最早开始时间
11  LASTTIME DATE;             --最晚结束时间
12  STR_SQL VARCHAR2(500);     --SQL语句字符串
13  ENDNUM NUMBER;             --时间段数量
14  BEGIN
15    --初始化总时长
16    TOTALTIME:=0;
17    --时间段个数：循环次数
18    EXECUTE IMMEDIATE('SELECT COUNT(*) '||CONDITION||' AND '||STARTTIMENAME||' IS NOT NULL ') INTO ENDNUM;
19    IF ENDNUM=0 THEN GOTO BREAK; END IF;
20  FOR SEQ IN 1..ENDNUM
21    LOOP
22      --拼写SQL语句：查询第SEQ行数据的开始时间和结束时间
23      STR_SQL:='SELECT num_to_date(ST),num_to_date(ED) FROM
24               (SELECT ROWNUM RN,T.ST,T.ED FROM
25               (SELECT DISTINCT '||STARTTIMENAME||' ST,'||ENDTIMENANAME||' ED '||CONDITION||
26               ' AND '||STARTTIMENAME||' IS NOT NULL ORDER BY '||STARTTIMENAME||') T) WHERE RN='||SEQ;
27 
28      BEGIN
29       EXECUTE IMMEDIATE STR_SQL INTO STARTTIME,ENDTIME;
30       EXCEPTION
31           WHEN NO_DATA_FOUND
32           THEN goto nextloop;
33       END;
34      --第一次循环
35      IF SEQ=1 THEN
36        FIRSTTIME:=STARTTIME;
37        LASTTIME:=ENDTIME;
38      --之后的循环
39      ELSIF STARTTIME>LASTTIME THEN
40        TOTALTIME:=TOTALTIME+(CASE WHEN DURATIONTYPE='nature' THEN (LASTTIME-FIRSTTIME) ELSE
41                               WORKTIMELENGTH.GETWORKTIMELEN(DURATIONTYPE,DATE_TO_NUM(FIRSTTIME),DATE_TO_NUM(LASTTIME))/(3600*24) END);
42        FIRSTTIME:=STARTTIME;
43        LASTTIME:=ENDTIME;
44      ELSIF STARTTIME<=LASTTIME AND ENDTIME>LASTTIME THEN
45        LASTTIME:=ENDTIME;
46      END IF;
47      <<nextloop>>
48       null;
49    END LOOP;
50    TOTALTIME:=TOTALTIME+(CASE WHEN DURATIONTYPE='nature' THEN (LASTTIME-FIRSTTIME) ELSE
51                           WORKTIMELENGTH.GETWORKTIMELEN(DURATIONTYPE,'0000'||DATE_TO_NUM(FIRSTTIME),'0000'||DATE_TO_NUM(LASTTIME))/(3600*24) END);
52    <<BREAK>>
53    RETURN TOTALTIME*24*3600;
54 END;

eg. REMOVE_REPEAT_TIME_SECONDS('表中开始时间字段名','表中结束时间字段名','from 该张表表名 where 条件','nature' )  

      结果单位：秒

 

常用查询条件

3、上一个月为条件

1 "时间(秒)" BETWEEN DATE_TO_NUM(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1)) AND DATE_TO_NUM(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1))

4、前一天为条件

1 "时间(秒)" BETWEEN DATE_TO_NUM(TRUNC(SYSDATE - 1)) AND DATE_TO_NUM(TRUNC(SYSDATE))

 

 

同步整理更新中......

 

转载于:https://www.cnblogs.com/macula/archive/2012/09/04/2670100.html