选读SQL经典实例笔记05_日期运算（下）.png
1. 两个日期之间相差的月份和年份
1.1. DB2
1.2. MySQL
1.3. sql
select mnth, mnth/12
from ( select (year(max_hd) - year(min_hd))*12 +
(month(max_hd) - month(min_hd)) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y
1.4. Oracle
1.4.1. sql
select months_between(max_hd,min_hd),
months_between(max_hd,min_hd)/12
from (
select min(hiredate) min_hd, max(hiredate) max_hd
from emp
) x
1.5. PostgreSQL
1.5.1. sql
select mnth, mnth/12
from (
select ( extract(year from max_hd) -
extract(year from min_hd) ) * 12
+
( extract(month from max_hd) -
extract(month from min_hd) ) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y
1.6. SQL Server
1.6.1. sql
select datediff(month,min_hd,max_hd),
datediff(month,min_hd,max_hd)/12
from (
select min(hiredate) min_hd, max(hiredate) max_hd
from emp
) x
2. 两个日期之间相差的秒数、分钟数和小时数
2.1. 相差的天数分别乘以24（一天的小时数），1440（一天的分钟数）和86400（一天的秒数）
2.2. DB2
2.2.1. sql
select dy*24 hr, dy*24*60 min, dy*24*60*60 sec
from (
select ( days(max(case when ename = 'WARD'
then hiredate
end)) -
days(max(case when ename = 'ALLEN'
then hiredate
end))
) as dy
from emp
) x
2.3. Oracle
2.4. PostgreSQL
2.5. sql
select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec
from (
select (max(case when ename = 'WARD'
then hiredate
end) -
max(case when ename = 'ALLEN'
then hiredate
end)) as dy
from emp
) x
2.6. MySQL
2.7. SQL Server
2.8. sql
select datediff(day,allen_hd,ward_hd)*24 hr,
datediff(day,allen_hd,ward_hd)*24*60 min,
datediff(day,allen_hd,ward_hd)*24*60*60 sec
from (
select max(case when ename = 'WARD'
then hiredate
end) as ward_hd,
max(case when ename = 'ALLEN'
then hiredate
end) as allen_hd
from emp
) x
3. 当前记录和下一条记录之间的日期差
3.1. DB2
3.1.1. sql
select x.*,
days(x.next_hd) - days(x.hiredate) diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate ＞ e.hiredate) next_hd
from emp e
where e.deptno = 10
) x
3.2. Oracle
3.2.1. sql
select ename, hiredate, next_hd,
next_hd - hiredate diff
from (
select deptno, ename, hiredate,
lead(hiredate)over(order by hiredate) next_hd
from emp
)
where deptno=10
3.3. PostgreSQL
3.3.1. sql
select x.*,
x.next_hd - x.hiredate as diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate ＞ e.hiredate) as next_hd
from emp e
where e.deptno = 10
) x
3.4. MySQL
3.5. SQL Server
3.6. sql
select x.*,
datediff(day,x.hiredate,x.next_hd) diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate ＞ e.hiredate) next_hd
from emp e
where e.deptno = 10
) x
3.6.2. datediff(x.next_hd, x.hiredate) diff
3.6.2.1. 对于MySQL 版本的DATEDIFF函数，需要省略第一个参数day，并把剩下的两个参数的顺序颠倒过来
4. 一年中有多少个星期一
4.1. 方案
4.1.1. 生成一年里所有可能的日期值
4.1.2. 格式化上述日期值，并找出它们分别是星期几
4.1.3. 统计每个“星期x”出现的次数
4.2. DB2
4.2.1. sql
with x (start_date,end_date)
as (
select start_date,
start_date + 1 year end_date
from (
select (current_date -
dayofyear(current_date) day)
+1 day as start_date
from t1
)tmp
union all
select start_date + 1 day, end_date
from x
where start_date + 1 day ＜ end_date
)
select dayname(start_date),count(*)
from x
group by dayname(start_date)
4.3. Oracle
4.3.1. sql
with x as (
select level lvl
from dual
connect by level ＜= (
add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)
)
select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
from x
group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')
4.3.2. sql
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,'DAY')
4.3.2.1. Oracle早期版本
4.4. PostgreSQL
4.4.1. sql
select to_char(
cast(
date_trunc('year',current_date)
as date) + gs.id-1,'DAY'),
count(*)
from generate_series(1,366) gs(id)
where gs.id ＜= (cast
( date_trunc('year',current_date) +
interval '12 month' as date) -
cast(date_trunc('year',current_date)
as date))
group by to_char(
cast(
date_trunc('year',current_date)
as date) + gs.id-1,'DAY')
4.5. MySQL
4.5.1. sql
select date_format(
date_add(
cast(
concat(year(current_date),'-01-01')
as date),
interval t500.id-1 day),
'%W') day,
count(*)
from t500
where t500.id ＜= datediff(
cast(
concat(year(current_date)+1,'-01-01')
as date),
cast(
concat(year(current_date),'-01-01')
as date))
group by date_format(
date_add(
cast(
concat(year(current_date),'-01-01')
as date),
interval t500.id-1 day),
'%W')
4.6. SQL Server
4.6.1. sql
with x (start_date,end_date)
as (
select start_date,
dateadd(year,1,start_date) end_date
from (
select cast(
cast(year(getdate()) as varchar) + '-01-01'
as datetime) start_date
from t1
) tmp
union all
select dateadd(day,1,start_date), end_date
from x
where dateadd(day,1,start_date) ＜ end_date
)
select datename(dw,start_date),count(*)
from x
group by datename(dw,start_date)
OPTION (MAXRECURSION 366)
