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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.