Postgresql: Calculate work days (none weekend days)
Friday, February 15th, 2008Coming 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..