如果是月份的话,oracle中可以使用add_months()函数来实现,如:
select TO_CHAR (ADD_MONTHS(SYSDATE, -12), 'YYYY-MM') from dual
select ADD_MONTHS(sysdate,-1) from dual
select TO_CHAR (ADD_MONTHS(SYSDATE, -12), 'YYYYMM') from dual
Oracle根据当前时间查询前7天的数据
select TO_DATE(TO_CHAR(SYSDATE - 7, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') from dual
Oracle 上周一到周日
select trunc(sysdate,'iw') - 7 from dual;
select trunc(sysdate,'iw') - 1 from dual;
Oracle 本周一到周日
select trunc(next_day(sysdate - 8, 1) + 1) from dual;
select trunc(next_day(sysdate - 8, 1) + 2) from dual;
select trunc(next_day(sysdate - 8, 1) + 3) from dual;
select trunc(next_day(sysdate - 8, 1) + 4) from dual;
select trunc(next_day(sysdate - 8, 1) + 5) from dual;
select trunc(next_day(sysdate - 8, 1) + 6) from dual;
select trunc(next_day(sysdate - 8, 1) + 7) from dual;
本月第一天
select TRUNC(SYSDATE, 'MM') from dual;
本月最后一天
select last_day(SYSDATE) from dual;
本年
select to_char(sysdate,'yyyy') from dual;
上月第一天
select TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') from dual;
上月最后一天
select LAST_DAY (ADD_MONTHS (SYSDATE, -1)) 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
SELECT ADD_MONTHS(sysdate,-12) AS 去年的今天 , ADD_MONTHS(sysdate,12) AS 明年的今天 FROM dual
1.年
SELECT TO_CHAR(SYSDATE,'YYYY')||'年' FROM DUAL;
2.月
SELECT TO_CHAR(SYSDATE,'MM')||'月' FROM DUAL;
3.日
SELECT TO_CHAR(SYSDATE,'DD')||'日' FROM DUAL;
4.季
SELECT TO_CHAR(SYSDATE,'Q')||'季' FROM DUAL;
5.周
SELECT TO_CHAR(SYSDATE,'IW')||'周' FROM DUAL;
二、EXTRACT 年、月、日
1.年
SELECT EXTRACT(YEAR FROM SYSDATE)||'年' FROM DUAL;
2.月
SELECT EXTRACT(MONTH FROM SYSDATE)||'月' FROM DUAL;
3.日
SELECT EXTRACT(DAY FROM SYSDATE)||'日' FROM DUAL;
三、上个星期一到星期天
SELECT
TO_CHAR(SYSDATE,'yyyymmdd')-TO_NUMBER(TO_CHAR(SYSDATE,'d')-1)-6,
TO_CHAR(SYSDATE,'yyyymmdd')-TO_NUMBER(TO_CHAR(SYSDATE,'d')-1) FROM DUAL;
四、1分钟前、1小时前、1月前、1年前
1.一分钟前
SELECT TO_CHAR(SYSDATE-INTERVAL '1' MINUTE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
4.1年前
SELECT TO_CHAR(SYSDATE-INTERVAL '1' YEAR,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
五、当月、上月、当天、前天
1.当月
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,0),'YYYY-MM-DD') FROM DUAL;
2.上月
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM-DD') FROM DUAL;
3.当天
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
4.前天
SELECT TO_CHAR(SYSDATE-1,'YYYY-MM-DD') FROM DUAL;
5.5小时前
SELECT (sysdate - 5/24) FROM DUAL;