Skip to Main Content
  • Questions
  • Using connect by level to generate dates and times

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, nor azan.

Asked: March 11, 2019 - 7:25 am UTC

Last updated: March 14, 2019 - 8:41 am UTC

Version: Oracle DB 12c

Viewed 10K+ times! This question is

You Asked

I have two SQL Queries:

SQL Query 1:
select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange  from dual 
connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);


SQL Query 2:
select level,to_char(trunc(sysdate) + (level-1)/24, 'YYYY-MM-DD HH24:MI') AS SysDater,to_char(trunc(sysdate) + (level-1)/24, 'HH24:MI') AS SysHour
from dual connect by level <= 24;



My Question is : I don't know to combine the above queries to get output like this:
Date        HRS(24)
-----       -------
1/1/2019      01:00
1/1/2019      02:00
1/1/2019      03:00
.             .
.             .
1/1/2019      24:00
1/2/2019      01:00
1/2/2019      02:00
.             .
1/2/2019      24:00
1/3/2019      01:00
1/3/2019      02:00
1/3/2019      03:00
.             .
.             .
1/3/2019      24:00



means every parameter dates will repeat 24 times(hrs)

Hopefully my question will be answered ASAP. Thank you.

and Chris said...

So you want to generate all the dates + hours between two dates?

You only need one query!

Just multiply the difference between the dates by 24. Then add on one hour ( 1/24 ) for each row:

var sDate varchar2(10);
var endDate varchar2(10);

exec :sDate := '01-01-2019';
exec :endDate := '03-01-2019';

select to_date ( :sDate,'dd-mm-yyyy' ) + 
         ( level / 24 ) dt
from   dual
connect by level <= ( 24 + ( 24 * 
    ( to_date(:endDate,'dd-mm-yyyy') - 
        to_date(:sDate,'dd-mm-yyyy') )
  ) 
) ;

DT                     
01-JAN-2019 01:00:00   
01-JAN-2019 02:00:00   
01-JAN-2019 03:00:00   
... 
03-JAN-2019 22:00:00   
03-JAN-2019 23:00:00   
04-JAN-2019 00:00:00   

72 rows selected. 

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

'Generate all the dates + hours between two dates' was answered.

nor azan Ab Aziz, March 13, 2019 - 7:17 am UTC


Thank you... my question was answered.

nor azan Ab Aziz, March 13, 2019 - 7:47 am UTC

Hi Chris,

How to get output like this:

DT
03-JAN-2019 22:00:00
03-JAN-2019 23:00:00
04-JAN-2019 00:00:00 --> Suppose to 03-JAN-2019 23:59:00


Means date still same day but the time for midnight I wanto to use 23:59
Chris Saxon
March 13, 2019 - 11:11 am UTC

Then you need to subtract one second (1/86400) from the final row in the result set.

Get the final row in the result set

nor azan Ab Aziz, March 14, 2019 - 4:59 am UTC

Hi, could you help me how to get the every 24th rows and make the time 23:59?


Thank you.
Chris Saxon
March 14, 2019 - 8:39 am UTC

See below.

avoiding the were-car

Racer I., March 14, 2019 - 8:23 am UTC

Hi,

select trunc(sysdate) - 2 + (level / 24) - CASE level WHEN 24 THEN 1/86400 ELSE 0 END dt
from   dual
connect by level <= (24 + (24 * (trunc(SYSDATE) - trunc(SYSDATE) + 2)));


regards,

...forever

Racer I., March 14, 2019 - 8:24 am UTC

select trunc(sysdate) - 2 + (level / 24) - CASE MOD(level, 24) WHEN 0 THEN 1/86400 ELSE 0 END dt
from   dual
connect by level <= (24 + (24 * (trunc(SYSDATE) - trunc(SYSDATE) + 2)));

Chris Saxon
March 14, 2019 - 8:41 am UTC

Nice stuff, thanks.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.