[Oracle] n초당 1개의 row만들기 : DATE 하루(86400 건) 만들기

 

connect by level과 

trunc(sysdate) 개념이 합쳐져서..

 

구글링의 결과 만세

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;


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

select to_char(trunc(sysdate) + 0.012 , 'YYYY-MM-DD HH24:MI') AS SysDater
from dual connect by level <= 24;

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

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

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

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

select to_char(trunc(sysdate) + (level-1)/86400, 'YYYY-MM-DD HH24:MI:SS') AS SysDater
from dual connect by level <= 20000; 

-- 3일치 하루에 2건씩은 어케하냐.. 아래는 안됨.
select to_char(trunc(sysdate) + ((level-1) + 86400-20000)/86400, 'YYYY-MM-DD HH24:MI:SS') AS SysDater
from dual 
connect by level <= 60000;


-- 4초마다 하나의 데이터 생성하기
-- 86400/4 = 21600. 즉, 하루에 21600건씩 조회가능!! wow 
select to_char(trunc(sysdate) + (level-1)/86400 * 4, 'YYYY-MM-DD HH24:MI:SS') AS SysDater
from dual connect by level <= 20000; 

 

asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9540408400346045219

Using connect by level to generate dates and times - Ask TOM