以下sql可用于存储过程或统计报表时获取时间,亦可封装时间工具类,提高代码性能
--最近x天
select to_char(trunc(to_date('2023-11-06','yyyy-MM-dd') - '1'),'yyyy-MM-dd') from dual;
select to_char(trunc(to_date('2023-11-06','yyyy-MM-dd') - 1),'yyyy-MM-dd') from dual;
--最近x周
select to_char(trunc(to_date('2023-11-06','yyyy-MM-dd')-to_char(to_date('2023-11-06','yyyy-MM-dd'), 'd') -(1 * 7) +2),'yyyy-MM-dd') from dual;
select to_char(trunc(to_date('2023-11-06','yyyy-MM-dd')-to_char(to_date('2023-11-06','yyyy-MM-dd'), 'd') +1) ,'yyyy-MM-dd') from dual;
--最近x月
select to_char(trunc(add_months(to_date('2023-11-06','yyyy-MM-dd'),- 1), 'mm'),'yyyy-MM-dd') from dual;
select to_char(trunc(last_day(add_months(to_date('2023-11-06','yyyy-MM-dd'),- 1))),'yyyy-MM-dd') from dual;
--最近x季度
select to_char(add_months(trunc(to_date('2023-11-06','yyyy-MM-dd'), 'Q'),-(1 * 3)),'yyyy-MM-dd') from dual;
select to_char(trunc(to_date('2023-11-06','yyyy-MM-dd'), 'Q') -1 ,'yyyy-MM-dd') from dual;
--最近x年
select to_char(add_months(trunc(to_date('2023-11-06','yyyy-MM-dd'), 'yyyy'), -(1 * 12)),'yyyy-MM-dd') from dual;
select to_char(trunc(to_date('2023-11-06','yyyy-MM-dd'), 'yyyy')-1 ,'yyyy-MM-dd') from dual;
可以优化的地方,希望各位可以提一下建议 ! ! !