Oracle Query With Every Minute A Day

I want to write a query in oracle sql, which returns every minute of the current day in one column. But i've got no idea, how to start :-( min 27.03.2014 00:00 27.03.2014 00:01 27.

Solution 1:

We can use arithmetic to manipulate dates, and the simple CONNECT BY trick to generate a stream of rows.

alter session set nls_date_format='dd-mon-yyyy hh24:mi'
/

with cte as (select trunc(sysdate) as start_date from dual )
select start_date + ((level-1)/(24*60)) as date_w_mins
from cte
connect by level <= (24*60)
/

Solution 2:

You can use numtodsinterval function and is simple too:

SELECT to_char(TRUNC(sysdate)+ 
               numtodsinterval (level-1,'minute'),'dd.mm.yyyy hh24:mi') min
FROM dual
CONNECTBY LEVEL <= (24*60);

Solution 3:

SELECTdate+minuteFROM
(
SELECT TO_DATE('yyyy.mm.dd', '2014.03.27') dateFROM DUAL
CROSSJOIN
( 
SELECT0minuteFROM DUAL
UNIONALLSELECT rownum r
FROM DUAL
CONNECTBY rownum <=1439
) T
) TT