如何在给定日期增加工作日数 [英] How to add number of business days to given date

问题描述

我正在寻找一个将给定日期增加工作日数的函数。

I'm looking for a function which adds number of business days to given date.

假期表

create table pyha (pyha date primary key) ;
insert into pyha values ('2018-12-24'),('2018-12-25'),('2018-12-26'),('2019-01-01');

包含假期。星期六和星期天也是非工作日。

contains holidays. Also saturday and sunday are non-business days.

我尝试创建函数

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$

with days as
(
    select dd, extract(DOW from dd) dw
    from generate_series(($1+ interval'1day')::date, ($1+ interval'1day'*$2+interval'10days')::date , '1 day'::interval) dd
)
select min(dd)::date
from   days
where  dw not in (6,0) and
dd not in (select pyha from pyha)
and dd>=$1+interval'1day'*$2+
interval'1day'*(select count(*) from pyha where pyha between $1+ interval'1day' and 
   $1+interval'1day'*$2 )
$fbd$ language sql;

但有时返回错误的结果：

But it returns sometimes incorrect result:

add_business_day('2018-12-08',2)  

返回2018-12-10

，但正确的结果是2018-12-11

returns 2018-12-10
but correct result is 2018-12-11

如何在Postgres 9.1+中创建此类功能？

How to create such function in Postgres 9.1+ ?

推荐答案

关键是生成一系列工作日，并用 row_number（）：

The key is to generate series of business days and number them with row_number():

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$
    select d
    from (
        select d::date, row_number() over (order by d)
        from generate_series(from_date+ 1, from_date+ num_days* 2+ 5, '1d') d
        where 
            extract('dow' from d) not in (0, 6) 
            and d not in (select pyha from pyha)
        ) s
    where row_number = num_days
$fbd$ language sql;

测试查询的结果似乎正确：

The test query's results seem correct:

select days, add_business_day('2018-12-08', days)
from generate_series(1, 20) days

 days | add_business_day 
------+------------------
    1 | 2018-12-10
    2 | 2018-12-11
    3 | 2018-12-12
    4 | 2018-12-13
    5 | 2018-12-14
    6 | 2018-12-17
    7 | 2018-12-18
    8 | 2018-12-19
    9 | 2018-12-20
   10 | 2018-12-21
   11 | 2018-12-27
   12 | 2018-12-28
   13 | 2018-12-31
   14 | 2019-01-02
   15 | 2019-01-03
   16 | 2019-01-04
   17 | 2019-01-07
   18 | 2019-01-08
   19 | 2019-01-09
   20 | 2019-01-10
(20 rows)

或者，您可以在以下位置找到日期循环：

Alternatively, you can find the date in a loop:

create or replace function add_business_day_loop(from_date date, num_days int)
returns date
as $fbd$
begin
    while num_days > 0 loop
        from_date:= from_date+ 1;
        while from_date in (select pyha from pyha) or extract('dow' from from_date) in (0, 6) loop
            from_date:= from_date+ 1;
        end loop;
        num_days:= num_days- 1;
    end loop;
    return from_date;
end;
$fbd$ language plpgsql;

这篇关于如何在给定日期增加工作日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！