Postgresql: Calculate work days (none weekend days)

Coming from a MsSQL database with access queries/macros/modules and so on, its easy to be able to work out weekend days from two given dates, ie if you have two dates you can calculate how many days between them aren’t weekdays.

In postgresql, this isnt so easy!

there aren’t many examples on the net, without creating mass functions, but i’ve come up with this:

SELECT count(*) FROM generate_series(1, (higherdate::date – lowerdate::date)) i WHERE date_part(‘dow’, higherdate::date + i) NOT IN (0,6);

the higherdate and lowerdate are exactly what they sound like, the higherdate has to be after the lowerdate.

That will work out how many days between each of those dates, and return a result that doesnt include sat/suns basically giving you working days!

It doesnt take into account holidays, but I didnt need it to..

Be Sociable, Share!
  • This is a test to prove that posting to blogs works, especially for terms such as label printer ie Mercian Labels.

  • Alessandro Pastore

    many thanks
    for me postgresql 8.4 it works with this sintax
    ‘extract dow’ etc.. instead of ‘date_part(‘dow’,’ etc..

    for example
    SELECT count(*) FROM generate_series(1, (‘2011-08-27’::date – ‘2011-07-02’::date)) AS i WHERE EXTRACT(DOW FROM ‘2011-08-27’::date + i) NOT IN (0,6);


  • Nerve

    Is this correct?

    IMHO the WHERE statement is wrong: WHERE EXTRACT(DOW FROM ’2011-08-27?::date + i) NOT IN (0,6);

    AS i returns an integer and the statement calculates the Day of Week from the higher date when it should use the lower date.

  • Both will work and produce the same results from what I can see.

    An alternative that also may work:

    select count(*) from generate_series(date ‘2011-08-03’, date ‘2011-08-08’, interval ‘1 day’) i where date_part(‘dow’, i) not in (0, 6);

  • Marco Harms

    Thanks for putting this up here. I found this by google because I needed this too. For future searchers: it should be “SELECT count(*)
    FROM generate_series(0, (higherdate::date – lowerdate::date::date)) i
    WHERE date_part(‘dow’, lowerdate::date::date + i) NOT IN (0,6);” or else the count is wrong.
    Eg. this returns 22 working days from 2014/04/01 to 2014/04/30 but the original version posted here returns 21.