手机

英国房地产因利率上升陷入困境房价正以2011年来最快速度下跌

宁夏评选出上半年10名“宁夏好人” 95后消防员因敬业奉献入选

家电

选读SQL经典实例笔记05_日期运算（下）

2023-07-12 07:02:43 来源：博客园

(资料图片仅供参考)

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)

关键词：