时间戳

首页 > 解决方案 > 如何在 postgres 中获取下一个业务日期？

sql - 如何在 postgres 中获取下一个业务日期？

问题描述

工作日为周一至周五。

鉴于我有一个 datetime 字段scheduled_for，我如何找到下一个营业日期并将其返回到列别名中？

我从另一个 SO 答案中尝试了一些东西，但它没有按预期工作。

EXTRACT(ISODOW FROM v.scheduled_for)::integer) % 7 as next_business_day,

错误：

Query 1 ERROR: ERROR:  syntax error at or near ")"
LINE 3:  EXTRACT(ISODOW FROM v.scheduled_for)::integer % 7)  as next...

                                                      ^

编辑：

感谢您的建议，我已经尝试过：

SELECT
    v.id AS visit_id,
    (IF extract(''dow'' from v.scheduled_for) = 0 THEN
        return v.scheduled_for + 1::integer;
    ELSIF extract(''dow'' from v.scheduled_for) = 6 THEN
        return v.scheduled_for - 1::integer;
    ELSE
        return v.scheduled_for;
    ) as next_business_day, 
    '' as invoice_ref_code,

我得到的错误是：

Query 1 ERROR: ERROR:  syntax error at or near ")"
LINE 1: ) as next_business_day, 
    ^

标签： sqlpostgresqldate

解决方案

为了概括，您需要创建一个函数来计算给定日期的下一个工作日。

create or replace function utl_next_business_day(date_in date default current_date)
returns date
language sql immutable leakproof strict
as $$
    with cd as (select  extract(isodow from date_in)::integer d)  
    select case when d between 1 and 4 
                then date_in + 1
                else date_in + 1 + (7-d) 
            end 
       from cd;       
$$; 

--- any single date 
select current_date, utl_next_business_day();

-- over time span (short)
select gdate::date for_date, utl_next_business_day(gdate::date) next_business_day
  from generate_series( current_date, current_date + 14, interval '1 day') gdate;

-- around year end over a time span
with test_date (dt) as
     ( values (date '2019-12-31')
            , (date '2020-12-31'), (date '2021-12-31'),(date '2022-12-31') 
            , (date '2021-01-01'), (date '2022-01-01'),(date '2023-01-01')
            )
select dt, utl_next_business_day(dt) from test_date
order by dt;

或者，我们得到@Eric 的日历表建议。

-- create and populate work table
create table bus_day_calendar ( bus_day date);
insert into bus_day_calendar (bus_day) 
  select utl_next_business_day(gdate::date)
    from generate_series( date '2018-12-31', date '2023-01-01', interval '1 day') gdate
   where extract(isodow from gdate)::integer not in (6,7) ;

--- Function to return next business day   
create or replace function utl_next_cal_business_day(date_in date default current_date)
returns date
language sql stable leakproof strict
as $$
    select min(bus_day)
      from bus_day_calendar 
     where bus_day > date_in;      
$$;    

--- any single date 
select current_date, utl_next_cal_business_day();

-- over time span (short)
select gdate::date for_date, utl_next_cal_business_day(gdate::date) next_business_day
  from generate_series( current_date, current_date + 14, interval '1 day') gdate;

-- around year end over a time span
with test_date (dt) as
     ( values (date '2019-12-31')
            , (date '2020-12-31'), (date '2021-12-31'),(date '2022-12-31') 
            , (date '2021-01-01'), (date '2022-01-01'),(date '2023-01-01')
            )
select dt, utl_next_cal_business_day(dt) from test_date
order by dt;

目前它们都不能处理周一至周五的非工作日，但两者都可以修改为这样做。由于日历表只需要删除 roes，我认为如果有必要，这将成为更好的方法。

推荐阅读