【Oracle】按时间范围查询

 

-- 七天前的那一天
SELECT T.*
  FROM T_BREAKER T
 WHERE T.GATHERTIME BETWEEN
       TO_DATE(TO_CHAR(SYSDATE - 7, 'yyyy-mm-dd') || ' 00:00:00',
               'yyyy-mm-dd hh24:mi:ss') AND
       TO_DATE(TO_CHAR(SYSDATE - 7, 'yyyy-mm-dd') || ' 23:59:59',
               'yyyy-mm-dd hh24:mi:ss');

-- 上个星期一到上个星期天
SELECT T.*
  FROM T_BREAKER T
 WHERE TO_CHAR(T.GATHERTIME, 'YYYY-MM-DD') BETWEEN
       TO_CHAR((TRUNC(SYSDATE, 'DY') + 1 - 7), 'YYYY-MM-DD') AND
       TO_CHAR((TRUNC(SYSDATE, 'DY')), 'YYYY-MM-DD');

/* 前一个月内, 上个月1号到上个月最后一天 */
SELECT TO_CHAR(SYSDATE, 'MM') - 1 AS ONE_MONTH_AGO FROM DUAL;

SELECT T.*
  FROM T_BREAKER T
 WHERE TO_CHAR(T.GATHERTIME, 'MM') = TO_CHAR(SYSDATE, 'MM') - 1;

/* 前3个月内 */
SELECT TO_CHAR(SYSDATE, 'MM') - 3 AS ONE_MONTH_AGO FROM DUAL;

SELECT T.*
  FROM T_BREAKER T 
  WHERE TO_CHAR(T.GATHERTIME, 'MM') BETWEEN TO_CHAR(SYSDATE, 'MM') - 3
  AND TO_CHAR(SYSDATE, 'MM') - 1;
  

/* 前半年内 */
SELECT T.*
  FROM T_BREAKER T 
  WHERE TO_CHAR(T.GATHERTIME, 'MM') BETWEEN TO_CHAR(SYSDATE, 'MM') - 6
  AND TO_CHAR(SYSDATE, 'MM') - 1
  ORDER BY T.GATHERTIME ASC;

/* 上一年内,去年1月1日到去年12月31日 */
SELECT T.*
  FROM T_BREAKER T
 WHERE TO_CHAR(T.GATHERTIME, 'yyyy') = EXTRACT(YEAR FROM SYSDATE) - 1;