求开始时间前1小时至结束时间后一小时的时间间隔日期,假设table表开始日期是2013-3-13 8:00:00,结束日期是2013-3-14 8:00:00,我想要的数据如下:日期
2013-3-13 7:00:00
2013-3-13 8:00:00
2013-3-13 9:00:00
.
.
.
2013-3-14 00:00:00
2013-3-14 1:00:00
.
.
.
2013-3-14 9:00:00请问这个SQL怎么写呢?
2013-3-13 7:00:00
2013-3-13 8:00:00
2013-3-13 9:00:00
.
.
.
2013-3-14 00:00:00
2013-3-14 1:00:00
.
.
.
2013-3-14 9:00:00请问这个SQL怎么写呢?
with t as
(select level - 1 n
from dual
connect by level <=
(select (to_date('2013-3-14 8:00:00', 'yyyy-mm-dd hh24:mi:ss') -
to_date('2013-3-13 8:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 24 + 1
from dual))
select n / 24 + to_date('2013-3-13 8:00:00', 'yyyy-mm-dd hh24:mi:ss') from t
with t as
(select level - 1 n
from dual
connect by level <=
(select (to_date('2013-3-14 9:00:00', 'yyyy-mm-dd hh24:mi:ss') -
to_date('2013-3-13 7:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 24 + 1
from dual))
select n / 24 + to_date('2013-3-13 7:00:00', 'yyyy-mm-dd hh24:mi:ss') from t少了2013-3-14 9:00:00的数据