CODEWITHSUNDEEP
oracle11g
ltran
ltran

Reputation: 25

Insert one row per hour between two date time range including duration (Oracle)

I have two date time records "start_date" and "end_date" from date_range table.

I would like to insert one row per hour for each hour interval, plus a column with the duration (in hours) so that my results return as follows:

e.g. start_date = 2016/09/01 21:12:00 and end_date = 2016/09/02 01:30:00

Date         Hour    Duration
2016/09/01   21      0.8
2016/09/01   22      1
2016/09/01   23      1
2016/09/02   00      1
2016/09/02   01      0.5

Upvotes: 0

Views: 676

Answers (3)

user5683823
user5683823

Reputation:

Here is a plain SQL solution (best to avoid PL/SQL when possible and not too complicated). It uses a recursive factored subquery, available since Oracle 11.1. I created several "rows" of test data to show how this might work for more than one pair of inputs at the same time. Please note, the first subquery is not part of the solution - you would replace it (and the references to it in the actual solution, which is the rest of the query) with your actual table and column names, or whatever your input source.

Note also that "date" and "hour" are reserved words in Oracle, and they shouldn't be used as column names (in the output or anywhere else). I used dt and hr instead.

with
     date_range ( row_id, start_date, end_date ) as (
       select 101, to_date('2016/09/01 21:12:00', 'yyyy/mm/dd hh24:mi:ss'),
              to_date('2016/09/02 01:30:00', 'yyyy/mm/dd hh24:mi:ss') from dual union all
       select 102, to_date('2016/09/02 21:00:00', 'yyyy/mm/dd hh24:mi:ss'),
              to_date('2016/09/02 22:00:00', 'yyyy/mm/dd hh24:mi:ss') from dual union all
       select 103, to_date('2016/09/01 15:00:00', 'yyyy/mm/dd hh24:mi:ss'),
              to_date('2016/09/01 15:30:00', 'yyyy/mm/dd hh24:mi:ss') from dual union all
       select 104, to_date('2016/09/01 21:12:00', 'yyyy/mm/dd hh24:mi:ss'),
              to_date('2016/09/01 21:30:00', 'yyyy/mm/dd hh24:mi:ss') from dual
     ),
     rec ( row_id, from_time, to_time, end_date ) as (
       select  row_id, start_date, 
                least(end_date, trunc(start_date, 'hh') + 1/24), end_date
         from  date_range
       union all
       select  row_id, to_time, least(end_date, to_time + 1/24), end_date
         from  rec
         where end_date > from_time + 1/24
     )
select row_id, 
       to_char(from_time, 'yyyy/mm/dd') as dt,
       to_char(from_time, 'hh24') as hr, 
       round(24 * (to_time - from_time), 2) as duration
from   rec
order by row_id, from_time
;

Output:

    ROW_ID DT         HR   DURATION
---------- ---------- -- ----------
       101 2016/09/01 21         .8
       101 2016/09/01 22          1
       101 2016/09/01 23          1
       101 2016/09/02 00          1
       101 2016/09/02 01         .5
       102 2016/09/02 21          1
       103 2016/09/01 15         .5
       104 2016/09/01 21         .3

 8 rows selected

Upvotes: 1

Sedat.Turan
Sedat.Turan

Reputation: 1

select case when lvl=mn then (trunc(frst)+1)- frst
            when lvl=mx then lst-trunc(lst) 
       else 1
       end ,a.*
        from  
(
select frst,lst,sonuc,lvl,first_value(lvl)over() mn,last_value(lvl) over() mx
   from  
(select to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss') frst,
        to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') lst,
       (to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') - to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss'))*24 sonuc from dual) a  
, 
( select level lvl 
   from dual
connect by level <=
(select
        case when (to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') - to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss'))*24> 
        trunc((to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') - to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss'))*24)
        then trunc((to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') - to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss'))*24)+1 
        else trunc((to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') - to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss'))*24)
        end 
   from dual)
) b
)a

Upvotes: 0

CompEng
CompEng

Reputation: 7376

You can do it with procedure like this:

first I prepare your data;

create table date_range 
(
start_date date,
end_date date
);

create table result_table
(
date_ varchar2(10),
hour_ varchar2(2),
duration number
);


insert into date_range    
select to_date('2016/09/01 21:12:00','YYYY/MM/DD hh24:mi:ss'),to_date('2016/09/02 01:30:00','YYYY/MM/DD hh24:mi:ss') from dual ;

commit;

second I create your procedure;

CREATE OR REPLACE procedure get_dates 
AS 
  rn number:=0;
  temp varchar2(10); 
BEGIN

 select 
ceil(24* ( end_date-start_date ))-1
into rn 
 from
date_range;


FOR  i IN 0..rn 
   LOOP

    temp:=i||'/24';

    EXECUTE IMMEDIATE '
       insert into result_table
         select 
        to_char(start_date+'||temp||',''yyyy/mm/dd'')date_,
        to_char(start_date+'||temp||',''hh24'')hour_,
        case 
          when '||i||'=0 
           then 1-to_number(to_char(start_date+'||temp||',''mi''))/60
          when '||i||'='||rn||' 
           then 1-to_number(to_char(end_date+'||temp||',''mi''))/60
           else 1
        end duration
        from
        date_range ';

        commit;

   END LOOP;



END get_dates;

thirdly I execute procedure ;

begin
get_dates();
end;

finally I see the expected result ;

select   
* from
result_table;



DATE_        HOUR_  DURATION
2016/09/01    21    0,8
2016/09/01    22    1
2016/09/01    23    1
2016/09/02    00    1
2016/09/02    01    0,5

Upvotes: 0

Related Questions