-- 七天前的那一天
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;