Oracle, split a time duration row by one hour period

Question

I'm working on oracle db and I'm not quite good at Oracle. I'm trying to split a row by one hour period.

For example, if a time row has given as below,

Start_time(yyyy/mm/dd hh24:mi:ss) | End_time(yyyy/mm/dd hh24:mi:ss)

2013/09/01 09:30:00         2013/09/01 11:38:59

The result I want to see like this:

2013/09/01 09:30:00         2013/09/01 09:59:59  
2013/09/01 10:00:00         2013/09/01 10:59:59
2013/09/01 11:00:00         2013/09/01 11:38:59

I've been searched how to do it but I couldn't find one.
But I guess It can be done by using 'CONNECT BY'.
Any help will be great. Thanks in advance.

Check this: http://stackoverflow.com/questions/17035176/need-oracle-sql-to-split-up-date-time-range-by-day — rags, Sep 16 '13 at 05:51
@rags Thank you for your link but it dealt with date. I'm looking for how I split a row by one hour period. — user2782719, Sep 16 '13 at 06:41

score 2 · Accepted Answer · answered Sep 16 '13 at 07:16

2

I have bulit a basic query, you can work around it and get what you want.

select greatest(Start_time, trunc(Start_time+(level-1)/24, 'hh24')), 
least(End_time, trunc(Start_time+(level)/24, 'hh24'))
from log_table 
connect by level <= floor((dt1-dt2)*24)+1;

Example at sqlfiddle:

http://sqlfiddle.com/#!4/82625/29

answered Sep 16 '13 at 07:16

DB_learner

1,026
9
15

Thank you Mani. Your help was great hint to get the result what I want . – user2782719 Sep 16 '13 at 09:08

score 0 · Answer 2 · edited Jul 27 '17 at 04:42

Slightly modified version of the query by . The earlier query did not work in some cases say from date/time is 2:37 am and to date is 6:10 am.

WITH date1 AS (
  SELECT to_date('2017-07-26 02:37:00 AM','yyyy-MM-dd hh12:mi:ss am') dt2,
         to_date('2017-07-26 06:10:00 PM','yyyy-MM-dd hh12:mi:ss am') dt1 FROM DUAL
)
select TO_CHAR (greatest(dt2, trunc(dt2+(level-1)/24, 'hh24')),
    'dd-MON-yyyy HH12:MI:SS AM') FROM_DT,
    TO_CHAR (  least(dt1, trunc(dt2+(level)/24, 'hh24'),
    'dd-MON-yyyy HH12:MI:SS AM') TO_DT
    from date1 connect by level <= floor(
        (trunc(dt1,'HH')-trunc(dt2,'HH'))  *24) + case when (
             dt1 <> trunc(dt1,'HH')
        ) then 1 else 0 end

Oracle, split a time duration row by one hour period

2 Answers2

Linked