Using Oracle’s CONNECT BY to generate time slices.
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...
