初学者计算日期之间的天数（不包括周末和节假日） [英] Beginner way to count days between dates excluding weekends and holidays

问题描述

我是Postgres的初学者，正在寻找有关查询问题的帮助。我试图计算两个日期之间的工作日数（不包括周六，周日和节假日）。这就是我要尝试的类型：

I am a beginner in Postgres and looking for some help on a problem I am having with a query. I am trying to count the number of business days between two dates (exclude sat & sun & holidays). This is kind of what I am trying to do:

Select column1, column2, (current_date - defined_date) as elapsed_days
from mytable
where elapsed_days excludes sat, sun, and holidays

推荐答案

create table calendar c as
(cal_date date primary key,
 business_day boolean not null);


 insert into calendar
 (select 
  ('01/01/1900'::date + (g||' days')::interval)::date,
 case extract(dow from '01/01/1900'::date 
    + (g||' days')::interval) 
 when 0 then false when 6 then false else true end
 from generate_series(0,365*150) g)

现在您有一个日历表，其中周末设置为 business_day = false，所有其他日期设置为true。

Now you have a calendar table populated with weekends set to "business_day=false" and all other days set to true.

您必须手动填充其他假期或编写程序来

You'll have to populate your other holidays manually or write a program to do that.

然后，要计算两天之间的差异，请执行以下操作：

Afterwards, to calculate difference between days do something like:

 select count(*) from cal 
 where cal between "start_date_var" and "end_date_var" 
 and business_day=true;

注意：如果是我，我会在您的日历表中添加其他几列，以便它可以包括它是哪个假期，或其他类似的东西。甚至可能有另一个假期表。不过，这是一个不错的开始。

NOTE: If it were me, I'd add a few other columns to your calendar table so that it can include which holiday it is, or other things like that. May even have another table for holidays. This is a good start though.

这篇关于初学者计算日期之间的天数（不包括周末和节假日）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！