sql cookbook笔记 第八章 日期运算

81 加减日，月，年
问题：对日期加减日，月，年进行加减各前5后5.使用ADD_MONTHS函数加减月数和年数。
select qsdate - 5 as minus_5d,
       qsdate + 5 as plus_5d,
       add_months(qsdate, -5) as minus_5M,
       add_months(qsdate, 5) as plus_5M,
       add_months(qsdate, -5 * 12) as minus_5y,
       add_months(qsdate, 5 * 12) as plus_5y
  from emp t
 where t.empid = '1';
82 计算两个日期之间的天数
问题：求两个日期之间相差的天数。
select x1 - x2
  from (select t2.qsdate as x1 from emp t2 where t2.empname = '1abcvd') x,
       (select t2.qsdate as x2 from emp t2 where t2.empname = '1bsdfds') y
83 确定两个日期之间的工作日数目
问题：给定两个日期，求它们之间（包括这两个日期本身）有多少个”工作“日。
select sum(case
             when to_char(jones_hd + t500.id - 1, 'DY') in ('SAT', 'SUN') then
              0
             else
              1
           end) as days
  from (select max(case
                     when ename = 'BLAKE' then
                      hiredate
                   end) as blake_hd,
               max(case
                     when ename = 'JONES' then
                      hiredate
                   end) as jones_hd
          from emp
         where ename in ('BLAKE', 'JONES')) x,
       t500
 where t500.id <= blake_hd - jones_hd + 1
84 确定两个日期之间的月份数或年数
问题：求两个日期之间相差的月数或年数。
解决方案：使用函数MONTHS_BETWEEN,将得到两个日期之间相差的月数（要得到相差的年数，只需除以12即可）：
select months_between(max_hd, min_hd), months_between(max_hd, min_hd) / 12
  from (select min(qsdate) min_hd, max(qsdate) max_hd
          from emp
         where empid = '1') x
85 确定两个日期之间的秒，分，小时数
问题：求两个日期之间相差的秒数，
select dy * 24 as hr, dy * 24 * 60 as min, dy * 24 * 60 * 60 as sec
  from (select (max(case
                      when empname = '1abcvd' then
                       qsdate
                    end) - max(case
                                  when empname = '1cdsf' then
                                   qsdate
                                end)) as dy
          from emp) x
86 计算一年中周内各日期的次数
问题：计算一年中周内各日期（星期日，星期一......星期六）的次数。
解决方案：要计算一年中周内各日期分别有多少个，必须：
1.生成一年内的所有日期。
2.设置日期格式，得到每个日期对应为星期几。
3.计数周内各日期分别有多少个。
对于ORACLE9I DATABASE或更高版本，可以使用递归来CONNECT BY 子句，返回一年内的所有日期。而对于ORACLE8I DATABASE或较早版本，则只需对表T500进行选择操作，就能生成包含一年内所有日期的行。另外，使用TO_CHAR函数，可确定每个日期为星期几，然后计数周内各日期的次数：
首先是CONNECT BY解决方案：
with x as (
select level lv1 from dual connect by level <=(add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')) )
select to_char(trunc(sysdate,'y')+lv1-1,'DAY'),COUNT(*) from x group by to_char(trunc(sysdate,'y')+lv1-1,'DAY')
接下来是ORACEL较早版本的解决方案：
select to_char(trunc(sysdate, 'y') + rownum + 1, 'DAY'), count(*)
  from t500
 where rownum <=
       (add_months(trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y'))
 group by to_char(trunc(sysdate, 'y') + rownum - 1, 'DAT')
87 确定当前记录和下一条记录之间相差的天数
问题：求两个日期之间相差的天数。
select empname, qsdate, empid, next_hd - qsdate diff
  from (select empid,
               empname,
               qsdate,
               lead(qsdate) over(order by qsdate) next_hd
          from emp)
 where empid = 1
在ORACLE8 DATABASE或较早版本中，可以采用POSTGRESQL解决方案：
select x.*, x.next_hd - x.qsdate as diff
  from (select e.empid,
               e.empname,
               e.qsdate,
               (select min(d.qsdate) from emp d where d.qsdate > e.qsdate) as next_hd
          from emp e
         where e.empid = '1') x