（013）每日SQL学习：日期的各种计算

1.确定两个日期之间的工作日天数

--确定两个日期之间的工作日天数
with x0 as 
(select to_date('2018-01-01','yyyy-mm-dd') as 日期 from dual
union all
select to_date('2018-01-15','yyyy-mm-dd') as 日期 from dual ),
x1 as --日期并列显示 
(select min (日期) 开始日期,max(日期) 结束日期 from x0 ),
x2 as --日期之间的天数
(select 结束日期-开始日期+1  as 天数 ,开始日期,结束日期 from x1),
x3 as --构造一个从开始日期到结束日期的日期集合 （枚举日期）
(select to_char(开始日期+level-1,'DY') as 枚举日期 from x2 connect by level <=天数)
--统计日期
select sum(case when 枚举日期 in ('sat','sun')then 0 else 1 end ) as 工作日 from x3

 2.计算一年周内各日期次数

--计算一年周内各日期次数
with x0 as 
(select to_date('2018-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(日期,'DAY') AS 星期 FROM X3)

SELECT 星期,count(*) from x4 group by 星期

 3.确定当前记录和下一条记录之间的相差天数

with x0 as 
(select '1'as name ,to_date('2018-01-01','yyyy-mm-dd') as 日期 from dual
union all
select '2' as name,to_date('2018-01-15','yyyy-mm-dd') as 日期 from dual
union all
select '3' as name, to_date('2018-01-26','yyyy-mm-dd') as 日期 from dual )

select 
name ,日期,next_d,(next_d-日期) as 相差天数
 from (
      select name ,日期,
      lead(日期,1,null) over (order by 日期) as next_d
      from x0
)

lead 函数见下文说明：（014）每日SQL学习：oracle下lag和lead分析函数

 4.日期操作

select 列,行
from (
select
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 日期,
to_char(sysdate,'hh24') as 时,
to_char(sysdate,'mm') as 分,
to_char(sysdate,'ss') as 秒,
to_char(sysdate,'dd') as 日,
to_char(sysdate,'mm') as 月,
to_char(sysdate,'yyyy') as 年,
to_char(sysdate,'ddd') as 年内第几天,
to_char(trunc(sysdate,'day'),'yyyy-mm-dd') as 周初,
to_char(trunc(sysdate,'mm'),'yyyy-mm-dd') as 月初,
to_char(last_day(sysdate),'yyyy-mm-dd')  as 月末,
to_char(add_months(trunc(sysdate,'mm'),1),'yyyy-mm-dd') as 下月初,
to_char(trunc(sysdate,'yy'),'yyyy-mm-dd') as 年初,
to_char(sysdate,'day') as 周几,
to_char(sysdate,'month') as 月份
from dual 
)
 unpivot( 行 for 列 in (日期,时,分,秒,日,月,年,年内第几天,周初,月初,月末,下月初,年初,周几,月份 ))

结果：

  说明：注意此处last_day用法，该函数返回的时分秒和参数中的一样。既：如果参数日期为 2018-03-03 12：25：34 那么last_day(参数)结果是：2018-03-31 12：25：34

用此函数做区间条件时要小心数据被过滤了。一个月的数据最好采用add_months(trunc(sysdate,'mm'),1)为区间条件。

5.时间间隔（interval）

select 
interval '50' month as "month",
interval '99' day as "day",
interval '80' hour as "hour",
interval '90' minute as "minute",
interval '3.99' second as "second",
interval '123-3' year(3) to month as "year to month"
from dual 

结果：

       month      day                 hour                 minute             second                        year to month
      +04-02     +99 00:00:00    +03 08:00:00    +00 01:30:00    +00 00:00:03.990000    +123-03
6.提取timestamp年月日函数（extract）

select systimestamp ,
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 

 说明：

1.extract不能提取date类型中的时分秒。

2.extract可以提取interval中的信息，而to_char()不行。

3.timestamp与date类型的区别:

1.比date类型更为精确，秒后面加了.0000

2.求日期差时 date类型的差值是天数，timestamp的差值的具体到时分秒。例如：

select to_date('2018-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss')-sysdate from dual;
结果：148.422858796296
select to_timestamp('2018-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss')-systimestamp from dual
结果：+000000148 10:08:54.229000000

 

7.周的计算

with x0 as 
(select trunc(sysdate)-trunc(sysdate,'yy') as d from dual ),
x1 as
( select trunc(trunc(sysdate,'yy')+level-1) as d1 from x0 connect by level<=d)
select d1,to_char(d1,'d') as dd,
to_char(d1,'day') as 星期,
next_day(d1,1) as 下个周日,
to_char(d1,'iw')第几周
from x1

 8.确定某月的第一个和最后一个“”周内某天“的日期

select next_day(trunc(sysdate,'mm')-1,2) as 第一个星期一 ,
next_day(last_day(trunc(sysdate))-7,2) as 最后一个星期一
from dual 

说明：next_day是“下一”的意思。每个星期第一天是周日，第二天才是周一。

9.创建本月日历

with x0 as 
/*取出当前日期月初月末*/
(select trunc(sysdate,'mm') as first_day,last_day(trunc(sysdate,'mm')) as last_day from dual),
x1 as 
/*这个月的天数*/
(select (last_day-first_day+1) as day_num ,first_day,last_day from x0 ),
x2 as 
/*枚举本月每一天*/
(select first_day+level-1 as dd from x1 connect by level<=day_num),
x3 as 
/*提取周信息*/
(select 
to_char(dd,'iw') 第几周 ,
to_char(dd,'dd') as 日期,
to_number(to_char(dd,'d')) 周几
from x2
 )
select min(case when 周几 ='2' then 日期 end ) as 周一,
max(case when 周几 ='3' then 日期 end) as 周二,
max(case when 周几 ='4' then 日期 end) as 周三,
max(case when 周几 ='5' then 日期 end) as 周四,
max(case when 周几 ='6' then 日期 end) as 周五,
max(case when 周几 ='7' then 日期 end) as 周六,
max(case when 周几 ='1' then 日期 end) as 周日
 from x3 group by 第几周 order by 第几周

结果：

 10.创建本年日历

with x0 as 
/*取出当前日期月初月末*/
(select trunc(sysdate,'yy') as first_day,add_months(trunc(sysdate,'yy'),12)-1 as last_day from dual),
x1 as 
/*这个月的天数*/
(select (last_day-first_day+1) as day_num ,first_day,last_day from x0 ),
x2 as 
/*枚举本月每一天*/
(select first_day+level-1 as dd from x1 connect by level<=day_num),
x3 as 
/*提取周信息*/
(select 
to_char(dd,'iw') 第几周 ,
to_char(dd,'mm') 月,
dd as 日期,
to_number(to_char(dd,'d')) 周几
from x2
 ),
 x4 as 
 /*修正十二月份53周变为第一周的问题*/
 ( select case when 月='12' and 第几周='01' then '53' else 第几周 end 第几周,日期, 周几 from x3 )
select min(case when 周几 ='2' then 日期 end ) as 周一,
max(case when 周几 ='3' then 日期 end) as 周二,
max(case when 周几 ='4' then 日期 end) as 周三,
max(case when 周几 ='5' then 日期 end) as 周四,
max(case when 周几 ='6' then 日期 end) as 周五,
max(case when 周几 ='7' then 日期 end) as 周六,
max(case when 周几 ='1' then 日期 end) as 周日
 from x4 group by 第几周 order by 第几周

 11.确定指定年份季度的开始日期和结束日期

 
select
sn as 季度,
(sn-1)*3+1 as 月份,
add_months(to_date(年,'yyyy'),(sn-1)*3) as 开始日期,
add_months(to_date(年,'yyyy'),sn*3)-1 as 结束日期
from (select '2013' as 年,level as sn from dual connect by level<=4)

结果：      

季度    月份    开始日期    结束日期
1    1    1    2013/3/1    2013/5/31
2    2    4    2013/6/1    2013/8/31
3    3    7    2013/9/1    2013/11/30
4    4    10    2013/12/1    2014/2/28