A very useful feature of Oracle is the ‘CONNECT BY’ command. I make use of this whenever I need to generate any SQL output that has any sequential data as a key to the query. For example, a report of the number of logins per day or per hour.

To use connect by in your query, simply add a block to the ‘from’ clause section of you query and then reference its values the way you would any other table.

Here’s a few examples that return a sequential range of date/times based on current sysdate. Whats nice about this is that the sysdate is a moving target so you data is always kept up to date!

select to_char(x.lvl, 'YYYY-MM-DD HH24') || ':00'
from (  SELECT sysdate - (level/24)  lvl
      	FROM dual
      	CONNECT BY LEVEL <= 24 ) x

Outputs:
2009-08-19 14:00,
2009-08-19 13:00,
2009-08-19 12:00,
2009-08-19 11:00,
2009-08-19 10:00

select to_char(x.lvl, 'YYYY-MM-DD HH24') || ':00'
from (  SELECT sysdate - (12*level/24) lvl
      	FROM dual
      	CONNECT BY LEVEL <= 30 ) x

Outputs:
2009-08-19 03:00,
2009-08-18 15:00,
2009-08-18 03:00,
2009-08-17 15:00,
2009-08-17 03:00

select to_char(x.lvl, 'YYYY-MM-DD')
from ( SELECT sysdate - level lvl
      	FROM dual
      	CONNECT BY LEVEL <= 30) x

Outputs:
2009-08-17,
2009-08-16,
2009-08-15,
2009-08-14,
2009-08-13,
etc...

Post to Twitter Tweet This Post