Oracle之日期运算

1、 日、月、年的加减

-- 1.1 日、月、年的加减
select
  sysdate as 当前日期, 
  sysdate - 8 as 减8日, 
  sysdate + 8 as 加8日, 
  add_months(sysdate, -8) as 减8个月,
  add_months(sysdate, 8) as 加8个月,
  add_months(sysdate, -8 * 12) as 减8年,
  add_months(sysdate, 8 * 12)as 加8年
from dual;

2、 时、分、秒的加减

-- 1.2 时、分、秒的加减
select
  sysdate as 当前日期, 
  sysdate - 8 / 24 as 减8小时,
  sysdate + 8 / 24 as 加8小时,
  sysdate + 8 / 24/ 60 as 减8分钟,
  sysdate + 8 / 24/ 60 as 加8分钟,
  sysdate + 8 / 24/ 60/ 60 as 减8秒,
  sysdate + 8 / 24/ 60/ 60 as 加8秒
from dual;

3、 日期间隔（时、分、秒）

-- 1.3 日期间隔（时、分、秒）
select
  tmp.dates as 间隔天数,
  tmp.dates * 24 as 间隔小数,
  tmp.dates * 24 * 60 as 间隔分钟,
  tmp.dates * 24 * 60 * 60 as 间隔秒
from ( 
  select 
    to_date('2015-12-11','yyyy-mm-dd') - to_date('2015-12-01','yyyy-mm-dd') dates 
  from dual
) tmp;

4、 日期 间隔（日、月、年）

-- 1.4 日期间隔（日、月、年）
select
  tmp.enddate - tmp.startdate as 间隔天数,
  months_between(tmp.enddate, tmp.startdate) 间隔月,
  months_between(tmp.enddate, tmp.startdate) / 12 间隔年
from ( 
  select
    to_date('2013-06-11','yyyy-mm-dd') startdate, 
    to_date('2015-12-01','yyyy-mm-dd') enddate 
  from dual
) tmp;

5、 确定两日期之间的工作天数

-- 1.5 确定两日期之间的工作天数
select count(1) as "工作总天数"
from (
  select
    tmp2.dates,
    to_char(tmp2.dates, 'DY', 'NLS_DATE_LANGUAGE = American') as dy
  from ( 
    select 
      tmp.mindate + (t500.id - 1) dates -- 日期
    from (
      select 
        to_date('2015-06-11','yyyy-mm-dd') mindate, 
        to_date('2015-12-01','yyyy-mm-dd') maxdate 
      from dual
    ) tmp,
    (
      select 
        level as id from dual 
        connect by level <= (to_date('2015-12-01','yyyy-mm-dd') - to_date('2015-06-11','yyyy-mm-dd')) + 1 
    ) t500
    where t500.id <= ( (tmp.maxdate - tmp.mindate) + 1)
  ) tmp2
) tmp3
where tmp3.dy not in ('SAT', 'SUN');

6、 计算一年中周内个日期的次数

-- 1.6 计算一年中周内个日期的次数
-- 枚举一年全天信息
with x0 as
  ( select to_date('2013-01-01', 'yyyy-mm-dd') as 年头 from dual ),
x1 as
  ( select 年头, add_months(年头, 12) as 下年头 from x0 ),
x2 as
  ( select 年头, 下年头, 下年头 - 年头 as 天数 from x1 ),
x3 as
  ( select 年头 + (LEVEL - 1) as 日期 from x2 connect by level <= 天数 ),
x4 as
  ( select 日期, to_char(日期, 'DY') as 星期 from x3 )

select tmp.*
from ( select 星期, count(1) as 天数 from x4 group by 星期 ) tmp
order by decode(星期, '星期一', 1, '星期二', 2, '星期三', 3, '星期四', 4, '星期五', 5, '星期六', 6, '星期日', 7);

-- 直接取出年内各星期的第一天和最后一天，在除以7
with x0 as
  ( select to_date('2013-01-01', 'yyyy-mm-dd') as 年头 from dual ),
x1 as
  ( select 年头, add_months(年头, 12) as 下年头 from x0 ),
x2 as
  ( select next_day(年头 - 1, level) as d1, next_day(下年头 - 8, level) as d2 from x1
    connect by level <= 7)
select to_char(d1, 'dy') as 星期, d1, d2 from x2;

with x0 as
  ( select to_date('2013-01-01', 'yyyy-mm-dd') as 年头 from dual ),
x1 as
  ( select 年头, add_months(年头, 12) as 下年头 from x0 ),
x2 as
  ( select next_day(年头 - 1, level) as d1, next_day(下年头 - 8, level) as d2 from x1
    connect by level <= 7)
select to_char(d1, 'dy') as 星期, ( ( d2 - d1) / 7 + 1) as 天数
from x2
order by decode(星期, '星期一', 1, '星期二', 2, '星期三', 3, '星期四', 4, '星期五', 5, '星期六', 6, '星期日', 7);

7、 确定当前记录和下条记录之间的相差的天数

-- 1.7 确定当前记录和下条记录之间的相差的天数
-- 生成数据
create or replace view test_hyq( id, ename, createDate) as
  select 1, 'CLARK', date '2015-12-08' from dual
  union all
  select 2, 'KING', date '2015-12-01' from dual
  union all
  select 3, 'MILLER', date '2015-12-15' from dual
  union all
  select 4, 'JACK', date '2015-12-30' from dual;

--获取下一条记录的日期
select 
  t.id,
  t.ename,
  t.createdate,
  lead(t.createdate) over( order by t.createdate) nextdate
from test_hyq t;

select
  tmp.id as "编号",
  tmp.ename as "姓名",
  tmp.createdate as "创建日期",
  tmp.nextdate as "下一条记录的创建日期",
  tmp.nextdate - tmp.createdate as "间隔天数"
from (
  select
    t.id as id,
    t.ename as ename,
    t.createdate as createdate,
    lead(t.createdate) over( order by t.createdate) nextdate
  from test_hyq t
) tmp;

结尾

参考：《Oracle 查询优化改写技巧与案例》一书