一、查询当天的前一小时时间段(系统当前时间为: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
结果为:
延伸一:
查询昨晚21点到今晚21点的数据
select TRUNC(SYSDATE-1)+21/24 startTime,TRUNC(SYSDATE)+21/24 endTime 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,
concat(to_char(sysdate,'yyyy')||to_char(add_months(sysdate,-1), '-MM-')||to_char(LAST_DAY(sysdate),'dd '),'23:59:59') end_time
from dual
结果为:
正确的写法为:
select to_char(LAST_DAY(add_months(sysdate, -2)) + 1, 'yyyy-MM-dd') start_time,
to_char(LAST_DAY(add_months(sysdate, -1)), 'yyyy-MM-dd') 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),'dd '),'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