oralce 查詢一天每隔15分鐘一個點 15分鐘
select to_char((daybegin + (level - 1) * 15 / 1440), 'hh24:mi') as this15time
from (select (trunc(sysdate, 'dd')) as daybegin from dual)
connect by level <= 96
oracle 每隔5分鐘的話,隻需要修改 15改成5 level <=改成288
select to_char((daybegin + (level - 1) * 5 / 1440), 'hh24:mi')||':00' as this5time
from (select (trunc(sysdate, 'dd')) as daybegin from dual)
connect by level <=288
以上參考該文章
查詢一個月的資料,每條資料間隔五分鐘,笛卡爾積查詢
select to_date(days||' '||this5time,'yyyy-mm-dd hh24:mi:ss') datetime from (
SELECT '2020-02-' || TRIM(TO_CHAR(ROWNUM, '00')) AS DAYS FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('2020-02-01', 'YYYY-MM-DD')), 'DD'))
),(
select to_char((daybegin + (level - 1) * 5 / 1440), 'hh24:mi')||':00' as this5time
from (select (trunc(sysdate, 'dd')) as daybegin from dual)
connect by level <=288
)
