Oracle Sql优化之日期的处理

1.时，分，秒，年，月，日等日期的常用取值方法

select hiredate,
          to_number(to_char(hiredate,'hh24')) 时,
          to_number(to_char(hiredate,'mi')) 分,
          to_number(to_char(hiredate,'ss')) 秒,
          to_number(to_char(hiredate,'dd')) 日,
          to_number(to_char(hiredate,'mm')) 月,
          to_number(to_char(hiredate,'yyyy')) 年,
          to_number(to_char(hiredate,'ddd')) 年内第几天,
          trunc(hiredate,'dd') 一天的开始,
          trunc(hiredate,'day') 周初,
          trunc(hiredate,'mm') 月初,
          last_day(hiredate) 月末,
          add_months( trunc(hiredate,'mm'),1) 下月初,
          trunc(hiredate,'yy') 年初,
          to_char(hiredate,'day') 周几,
          to_char(hiredate,'month') 月份
 from emp

2.INTERVAL:INTERVAL类型中保存的是时间间隔信息，可以通过对应的INTERVAL 函数得到INTERVAL类型的数据。

select INTERVAL '2' year as "year",
          INTERVAL '50' month as "month",
          INTERVAL '99' day as "day",
          INTERVAL '80' hour as "hour",
          INTERVAL '90' minute as "minute",
          INTERVAL '3.15' second as "second",
          INTERVAL '2 12:30:59' DAY to second as "DAY to second",
          INTERVAL '13-3' year to month as "year to month" 
from dual;

3.EXTRACT:与TO_CHAR 一样，EXTRACT可以提取时间字段中的年月日时分秒，不同的是EXTRACT返回的是NUMBER类型，但是EXTRACT不能提取DATE类型中国的时分秒。

select EXTRACT(YEAR from systimestamp) as "year" from dual;

select created,EXTRACT(DAY from created) from dba_objects;

----EXTRACT可以提取INTERVAL中的信息,to_char不行，to_char可以获取日期中的时分秒

select EXTRACT(hour from it) as "hour"
from (select INTERVAL '2 12:30:59‘ DAY to second as it from dual);

4.确定某一年是否为闰年，检查二月月末时哪一天

select to_char(last_day(add_months(trunc(hiredate,'y'),1)),'DD')  as "日"  from emp

5.周的计算,WW和IW的区别:WW算法为每年的一月一日为第一周的开发，date+6为一周接受，IW的算法为星期一至星期日算一周。next_day(date,1||2)，1表示下个周末，2 表示下个周一

with x as
   (select trunc(sysdate,'YY')+(level-1) as 日期 from dual connect by level<=8)
select 日期, to_char(日期,'d') as d,to_char(日期,'day') as day,next_day(日期,1),
           to_char(日期,'ww') as ww, to_char(日期,'iw') as iw from x;

6.本月日历，枚举指定月份所有的日期，转到对应的周信息。

with x1 as
 (select to_date('2013-06-01','yyyy-mm-dd') as curdate from dual),
x2  as
   (select trunc(curdate,'mm') as bm, add_months(trunc(curdate,'mm'),1) as bnm from x1),
x3 as
  (select bm+(level-1) as day from x2 connect by level<=(bnm-bm)),
x4 as
  (select to_char(day,'iw') as inWeek,to_char(day,'dd') dd,to_number(to_char(day,'d')) nweek from x3)
select max(case nweek when 2 then dd end) Mon,
          max(case nweek when 3 then dd end) Feb,
          max(case nweek when 4 then dd end) Wen,
          max(case nweek when 5 then dd end) Thu,
          max(case nweek when 6 then dd end) Fri,
          max(case nweek when 7 then dd end) Sat,
          max(case nweek when 1 then dd end) Sun
from x4 
group by inWeek
order by inWeek;

7.Oracle的两个分析函数Lag和lead，分别用户访问结果集中的前一行和后一行

select empno,ename,proj_id,proj_start,
          lag(proj_end) over (partition by empno order by proj_start) as lastfinishedproj,
          proj_end;
          lag(proj_id) over(partition by empno order by proj_start) as lastprojno
from emp