db2 返回连续的日期时间

1 WITH n(level, time) AS 
2           (SELECT 1, starttime 
3              FROM t1
4              WHERE condition   --t1随意,用于确定一条唯一的记录将starttime插入到结果集
5            UNION ALL
6            SELECT n.level + 1,time+1 hour  --1 hour为步长
7              FROM t1, n
8              WHERE n.time<= endtime)
9 SELECT level, time FROM n;

更多递归查询信息请参考 http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0510rielau/

例子:

with  n(level, bb)  as 

	(select  1,to_date('2013-03-01 12:23:45','yyyy-mm-dd hh24:mi:ss')  
            from item a where  a.name='O3'	
        union all 
        select  n.level+1 ,n.bb+1  hour  
            from item b ,n where b.name='O3'  and n.bb<=to_date('2013-04-01 12:23:45','yyyy-mm-dd hh24:mi:ss'))

select  *  from n ;

结果截图:

转载于:https://my.oschina.net/zhtianwen/blog/143059