需求是这样的:
比如datea:2009-10-12 12:12:12,dateb:2009-10-13 12:12:12
求datea和dateb之间每秒的时间,每分钟的时间显示的SQL,要求显示的每秒的时间如下:
2009-10-12 12:12:12
2009-10-12 12:12:13
2009-10-12 12:12:14
2009-10-12 12:12:15
.
.
.要求显示的每分的时间如下:
2009-10-12 12:12
2009-10-12 12:13
2009-10-12 12:14
2009-10-12 12:15
.
.
.
比如datea:2009-10-12 12:12:12,dateb:2009-10-13 12:12:12
求datea和dateb之间每秒的时间,每分钟的时间显示的SQL,要求显示的每秒的时间如下:
2009-10-12 12:12:12
2009-10-12 12:12:13
2009-10-12 12:12:14
2009-10-12 12:12:15
.
.
.要求显示的每分的时间如下:
2009-10-12 12:12
2009-10-12 12:13
2009-10-12 12:14
2009-10-12 12:15
.
.
.
from dual
connect by rownum <= (to_date('2009-10-13 12:12:12','yyyy-mm-dd hh24:mi:ss') - to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss')) * 24 * 60;这是每分的,每秒的相应计算处理。
分
select to_char(minutes,'yyyy-mm-dd hh24:mi') from
(
select to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss') + (rownum-1)/24/60 as minutes
from dual
connect by rownum <= (to_date('2009-10-13 12:12:12','yyyy-mm-dd hh24:mi:ss') -
to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss')) * 24 * 60
) a秒
select to_char(seconds,'yyyy-mm-dd hh24:mi:ss') from (
select to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss') + (rownum-1)/24/60/60 as seconds
from dual
connect by rownum <= (to_date('2009-10-13 12:12:12','yyyy-mm-dd hh24:mi:ss') -
to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss')) * 24 * 60 *60
)
connect by rownum <= (2012 - 2009) + 1;select to_char(add_months(to_date('2009'||'01','yyyymm'),rownum - 1),'yyyy-mm') from dual
connect by rownum <= ((2012 - 2009) + 1) * 12;
执行结果:
SQL> select to_char(add_months(to_date('2009'||'01','yyyymm'),rownum - 1),'yyyy-mm') from dual
2 connect by rownum <= ((2012 - 2009) + 1) * 12;
TO_CHAR(ADD_MONTHS(TO_DATE('20
------------------------------
2009-01
2009-02
2009-03
2009-04
2009-05
2009-06
2009-07
2009-08
2009-09
2009-10
2009-11
2009-12
2010-01
2010-02
2010-03
2010-04
2010-05
2010-06
2010-07
2010-08
TO_CHAR(ADD_MONTHS(TO_DATE('20
------------------------------
2010-09
2010-10
2010-11
2010-12
2011-01
2011-02
2011-03
2011-04
2011-05
2011-06
2011-07
2011-08
2011-09
2011-10
2011-11
2011-12
2012-01
2012-02
2012-03
2012-04
2012-05
TO_CHAR(ADD_MONTHS(TO_DATE('20
------------------------------
2012-06
2012-07
2012-08
2012-09
2012-10
2012-11
2012-12
2013-01到2013去了,还有请各位大侠帮忙,要是求间隔N月的时间段和N年的时间段怎么求?比如:
2009-01
2009-03
2009-05
.
.
.
.
2009
2011
2013
.
.
.
.
问这么零散的问题,也太不专业点了吧