一、查询当天的前一小时时间段(系统当前时间为:2017-01-06 12:32:20)

select concat(to_char(sysdate,'yyyy-mm-dd ')||(to_char(sysdate,'hh24')-1),':00:00') start_time,concat(to_char(sysdate,'yyyy-mm-dd hh24'),':00:00') end_time from dual

二、查询前一天的时间段

select to_char(sysdate-1,'yyyy-mm-dd ')||'00:00:00' start_time,to_char(sysdate-1,'yyyy-mm-dd ')||'23:59:59' end_time  from dual

三、查询前一个月的时间段--有bug，不建议这样用,正确的应该是2016年12月才对的

select concat(to_char(sysdate,'yyyy')||to_char(add_months(sysdate,-1),'-MM-'),'01 00:00:00')start_time,'-MM-')||to_char(LAST_DAY(sysdate),'dd '),'23:59:59') end_time     
from dual

四、查询前一年的时间段

select extract(year from sysdate)-1||'-01-01 00:00:00' start_time,concat(extract(year from sysdate)-1||'-12-'||to_char(LAST_DAY(sysdate),'23:59:59') end_time   
from dual

五、查询当天的前1小时-->当前时间段

select t.* from THIRDPAY_DAY_SEQ t 
where t.CREATE_TIME between SYSDATE-numtodsinterval(1,'hour') and SYSDATE 
order by t.CREATE_TIME ASC

select t.* from THIRDPAY_DAY_SEQ t 
where t.CREATE_TIME between SYSDATE-numtodsinterval(60,'minute') and SYSDATE
order by t.CREATE_TIME ASC

select t.* from THIRDPAY_DAY_SEQ t 
where t.CREATE_TIME between SYSDATE-1 and SYSDATE-numtodsinterval(15,'minute') 
order by t.CREATE_TIME ASC