1

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
Frank O
  • 11
  • 1
  • 2
    You probably need a table containing all holidays and maybe weekends for the time range in question. – Thorsten Kettner Feb 06 '17 at 20:01
  • 1
    There is no easy way. It would at least be possible if you had a list of holidays. – Gordon Linoff Feb 06 '17 at 20:13
  • Is there an easy way if I skip the holidays and just exclude the weekends? Thanks! – Frank O Feb 06 '17 at 20:18
  • See my answer below.. or use the "generate_series" part of my population query in your normal query and include "where extract(dow from cal_date) not in (0,6) – Joe Love Feb 06 '17 at 21:28
  • If you want to exclude just weekends, there are plenty of questions here ([f.ex. here](http://stackoverflow.com/questions/41877136/how-to-count-days-except-sundays-between-two-dates-in-postgres)) -- If you want to exclude holidays too, you'll need a `COUNT(*)`-based solution, with a table, which contains the desired holidays to exclude (and maybe *some* weekend days to include, because in some countries, there are some extra holidays, which are just "moved" to a weekend day). PostgreSQL has no database/table built-in, which contains that data (for obvious reasons). – pozs Feb 07 '17 at 10:22

1 Answers1

0
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)

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.

Joe Love
  • 5,594
  • 2
  • 20
  • 32
  • What is reasone to not store only holidays? – Roman Tkachuk Feb 06 '17 at 20:30
  • In my opinion, it's less logic on the coders side.. they can always determine weekdays using extract(dow from...). With this, you could index the business_day column and get really fast results. To me, it just makes your code cleaner, but it does create a table for you to maintain forever. I suppose you could simply insert non-business days and use a not exists(), but with this method, it can have more widespread use-- tag days, even "business day" holidays ,etc... Depends on what you need and how much SQL you want to write. – Joe Love Feb 06 '17 at 20:51
  • 1
    Thank you for your help! I was able to resolve this with your help! – Frank O Feb 07 '17 at 01:34
  • If this answer is correct, remember to mark it as the correct answer. – Joe Love Feb 07 '17 at 23:30