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..
Tags: business days, Postgresql, work days, work_days()
March 6th, 2008 at 12:39 pm
This is a test to prove that posting to blogs works, especially for terms such as label printer ie Mercian Labels.