时间sql

oracle时间的sql

1.某一天的24小时

写法1：select  (trunc(to_date('2013-07-01', 'yyyy-mm-dd')) + trunc(to_char(to_date('2020-07-01', 'yyyy-mm-dd'), 'sssss') / 1800) / 48) + (ROWNUM - 1) / 48 as dates from dual
CONNECT BY ROWNUM <= (to_date('2013-07-02', 'yyyy-mm-dd') - to_date('2013-07-01', 'yyyy-mm-dd')) * 48 + 1;

结果：

2013-07-01 00:00:00
2013-07-01 00:30:00                 
2013-07-01 01:00:00
2013-07-01 01:30:00
2013-07-01 02:00:00
2013-07-01 02:30:00
2013-07-01 03:00:00
2013-07-01 03:30:00
2013-07-01 04:00:00
2013-07-01 04:30:00
2013-07-01 05:00:00
2013-07-01 05:30:00
2013-07-01 06:00:00
2013-07-01 06:30:00
2013-07-01 07:00:00
2013-07-01 07:30:00
2013-07-01 08:00:00
2013-07-01 08:30:00
2013-07-01 09:00:00
2013-07-01 09:30:00
2013-07-01 10:00:00
2013-07-01 10:30:00
2013-07-01 11:00:00
2013-07-01 11:30:00
2013-07-01 12:00:00
2013-07-01 12:30:00
2013-07-01 13:00:00
2013-07-01 13:30:00
2013-07-01 14:00:00
2013-07-01 14:30:00
2013-07-01 15:00:00
2013-07-01 15:30:00
2013-07-01 16:00:00
2013-07-01 16:30:00
2013-07-01 17:00:00
2013-07-01 17:30:00
2013-07-01 18:00:00
2013-07-01 18:30:00
2013-07-01 19:00:00
2013-07-01 19:30:00
2013-07-01 20:00:00
2013-07-01 20:30:00
2013-07-01 21:00:00
2013-07-01 21:30:00
2013-07-01 22:00:00
2013-07-01 22:30:00
2013-07-01 23:00:00
2013-07-01 23:30:00
2013-07-02 00:00:00

方法2：

select  date '2020-10-09' + (rownum - 1)*0.5/24 dt from  dual connect by rownum <= 49;