(8/14) oracle查询优化的改写笔记第八章日期操作

快来打我* 2023-02-21 06:10 85阅读 0赞

-- 第八章 日期操作
    -- 8.1 提取日期中的信息
    select hiredate,
    	to_date(to_char(hiredate,'yyyy-mm')  || '-1','yyyy-mm-dd') month_begin
    from emp
    where rownum<=1;
    
    select hiredate,trunc(hiredate,'mm') as month_begin
    from emp
    where rownum<=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 (select hiredate+30/24/60/60+20/24/20+5/24 as hiredate from emp where rownum<=1);
    
    select last_day(d)
    from (
    	select to_date('1980-12-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss') as d from dual
    	union all
    	select to_date('1980-12-15 05:20:30', 'yyyy-mm-dd hh24:mi:ss') as d from dual
    );
    
    with v1 as
    (select to_date('1980-12-15 05:20:30', 'yyyy-mm-dd hh24:mi:ss') as d1 from dual),
    v2 as
    (
    	select to_date('1980-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') as d2 from dual
    	union all
    	select to_date('1980-12-31 06:20:30','yyyy-mm-dd hh24:mi:ss') as d2 from dual
    )
    select v2.*
    from v1,
    	v2
    where v2.d2 between trunc(d1,'mm') and last_day(d1);
    
    -- 8.2 提取间隔类型中的信息
    with v1 as 
    (select timestamp '1981-01-02 11:02:33.55' as t1,
    	timestamp '1981-01-01 16:30:00.30' as t2
    from dual
    )
    select t1-t2,
    	to_char(t1-t2,'hh') as hh
    from v1;
    
    drop table t8_2;
    create table t8_2 as
    select extract(year from systimestamp) as "year",
    	extract(month from systimestamp ) as "month",
    	extract(day from systimestamp ) as "day",
    	extract(hour from systimestamp ) as "hour",
    	extract(minute from systimestamp ) as "minute",
    	extract(second from systimestamp ) as "second"
    from dual;
    
    desc t8_2;
    
    select * from t8_2;
    
    with v1 as
    (select timestamp'1981-01-02 11:02:35.55' as t1,
    	timestamp'1981-01-01 16:30:00.30' as t2
    from dual
    )
    select extract(day from t1-t2 )*24*60 +
    	extract(hour from t1-t2)*60 + extract(minute from t1-t2) as minute
    from v1;
    
    -- 8.3 周的计算
    with x as
    (select date'2014-01-01' as d from dual)
    select to_char(d,'dy') as wdl, to_char(d,'day')as wd2, to_char(d,'d') as wd3 from x;
    
    with v1 as
    (select date'2014-01-01' +(level-1) as d from dual connect by level<=7)
    select d,
    	to_char(d,'d'),
    	to_char(d,'dy')
    from v1;
    
    with v1 as
    (select date'2014-01-01' +(level-1) as d from dual connect by level<=7)
    select d
    from v1 where to_char(d,'d') = 3;
    
    with v1 as
    (select date'2014-01-01'+（level-2) as d
    from dual
    connect by level<=8
    )
    select d,to_char(d,'dy') ,next_day(d, 3) from v1;
    
    select next_day(date'2014-01-01' -1 ,3) from dual;
    
    select next_day(last_day(date'2014-01-01') -7 ,3) from dual;
    
    -- 8.4 计算一年内各日期的次数
    with x0 as
    (select to_date('2013-01-01','yyyy-mm-dd') as year_begin from dual)
    select year_begin, add_months(year_begin, 12) as next_year_begin from x0;
    
    with x0 as
    (select to_date('2013-01-01','yyyy-mm-dd') as year_begin from dual),
    x1 as
    (select year_begin, add_months(year_begin, 12) as next_year_begin from x0)
    select year_begin, next_year_begin,next_year_begin - year_begin as days from x1;
    
    with x0 as
    (select to_date('2013-01-01','yyyy-mm-dd') as year_begin from dual),
    x1 as
    (select year_begin, add_months(year_begin, 12) as next_year_begin from x0),
    x2 as
    (select year_begin, next_year_begin,next_year_begin - year_begin as days from x1)
    select year_begin + (level-1) as day from x2 connect by level<=days;
    
    with x0 as
    (select to_date('2013-01-01','yyyy-mm-dd') as year_begin from dual),
    x1 as
    (select year_begin, add_months(year_begin, 12) as next_year_begin from x0),
    x2 as
    (select year_begin, next_year_begin,next_year_begin - year_begin as days from x1),
    x3 as
    (select year_begin + (level-1) as day from x2 connect by level<=days)
    select day,to_char(day,'dy') as weekday from x3;
    
    with x0 as
    (select to_date('2013-01-01','yyyy-mm-dd') as year_begin from dual),
    x1 as
    (select year_begin, add_months(year_begin, 12) as next_year_begin from x0),
    x2 as
    (select year_begin, next_year_begin,next_year_begin - year_begin as days from x1),
    x3 as
    (select year_begin + (level-1) as day from x2 connect by level<=days),
    x4 as
    (select day,to_char(day,'dy') as weekday from x3)
    select weekday, count(*) as days from x4 group by weekday;
    
    with x0 as
    (select to_date('2013-01-01','yyyy-mm-dd') as year_begin from dual),
    x1 as
    (select year_begin,add_months(year_begin, 12) as next_year_begin from x0),
    x2 as
    (select next_day(year_begin-1, level) as d1, next_day(next_year_begin-8,level) as d2
    from x1
    connect by level<=7
    )
    select to_char(d1, 'dy') as weekday, ((d2-d1) /7 +1) as days
    from x2
    order by 1;
    
    -- 8.5 确定一年是否为闰年
    with v1 as
    (select date'2016-03-15' as d from dual)
    select trunc(d,'yyyy') as year_begin,
    	add_months(trunc(d,'yyyy'), 12) as next_year_begin,
    	add_months(trunc(d,'yyyy'), 12)-1 as year_end,
    	to_char(add_months(trunc(d,'yyyy'), 12)-1, 'ddd') as year_dayds
    from v1;
    
    -- 8.6 创建本月日历
    create or replace view emp_project(empno, ename, proj_id, proj_start, proj_end) as 
    select 7782,'CLARK',1,date'2005-06-16',date'2005-06-18' from dual 
    union all
    select 7782,'CLARK',4,date'2005-06-19',date'2005-06-24' from dual 
    union all
    select 7782,'CLARK',7,date'2005-06-22',date'2005-06-25' from dual; 
    
    select a.empno, 
    	a.ename,
    	a.proj_id,
    	a.start_date,
    	a.end_date,
    	case
    		when last_end_date >= start_date
    		then
    		'(工程' || lpad(a.proj_id, 2, '0') || ') 与工程 (' || lpad(a.last_proj_id,2, '0') || ')重复'
    	end as description
    from	(select empno,
    		ename,
    		proj_id,
    		proj_start as start_date,
    		lag(proj_end) over (partition by empno order by proj_start) as last_end_date,
    		proj_end as end_date,
    		lag(proj_id) over (partition by empno order by proj_start) as last_proj_id
    	from emp_project ) a
    order by 1,4;
    
    select *
    	from emp_project;