Oracle 日、月、周循环

Oracle SQL实现日、月、周循环
许多业务报表系统都是按日、月、周时间区间统计数据，通常都是通过job调用存储过程实现。
涉及到历史数据跑批时需要实现区间时间段按日、周、月循环。

-- 时间区间按日循环 
declare 
  -- Local variables here
  i integer;
  START_TIME DATE:=TO_DATE( '2020-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  END_TIME DATE:=TO_DATE( '2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  START_TIME_STR VARCHAR2(20);
  END_TIME_STR VARCHAR2(20);
begin
  -- Test statements here
   while START_TIME < END_TIME  loop
     START_TIME_STR :=to_char(START_TIME,'yyyy-mm-dd')||' 00:00:00';
     END_TIME_STR := to_char(START_TIME,'yyyy-mm-dd')||' 23:59:59';
     DBMS_OUTPUT.PUT_LINE(START_TIME_STR||'    '||END_TIME_STR);
     --DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||'    '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
     START_TIME:= START_TIME + 1;
  end loop;
end;


-- 时间区间按月循环 
declare 
  -- Local variables here
  i integer;
  START_TIME DATE:=TO_DATE( '2019-08-01', 'yyyy-mm-dd');
  END_TIME DATE:=TO_DATE( '2020-10-01', 'yyyy-mm-dd');
  START_TIME_STR VARCHAR2(20);
  END_TIME_STR VARCHAR2(20);
begin
  -- Test statements here
   while START_TIME < END_TIME  loop
     START_TIME_STR :=to_char(trunc(add_months(last_day(START_TIME), -1) + 1), 'yyyy-mm-dd')||' 00:00:00';
     END_TIME_STR := to_char(last_day(START_TIME), 'yyyy-mm-dd')||' 23:59:59';
     DBMS_OUTPUT.PUT_LINE(START_TIME_STR||'    '||END_TIME_STR);
     --DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||'    '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
     START_TIME:= add_months(START_TIME,1);
  end loop;
end;


-- 时间区间按周循环 
declare 
  -- Local variables here
  i integer;
  START_TIME DATE:=TO_DATE( '2019-08-01', 'yyyy-mm-dd');
  END_TIME DATE:=TO_DATE( '2020-10-01', 'yyyy-mm-dd');
  START_TIME_STR VARCHAR2(20);
  END_TIME_STR VARCHAR2(20);
begin
  -- Test statements here
   while START_TIME < END_TIME  loop
     START_TIME_STR :=to_char( trunc(START_TIME,'iw') , 'yyyy-mm-dd')||' 00:00:00';
     END_TIME_STR := to_char( trunc(START_TIME,'iw')+6, 'yyyy-mm-dd')||' 23:59:59';
     DBMS_OUTPUT.PUT_LINE(START_TIME_STR||'    '||END_TIME_STR);
     --DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||'    '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
     START_TIME:= trunc(START_TIME,'iw')+7;
  end loop;
end;

D552281B-56B8-4f2d-B690-845B884A14FD.png