Postgresql: Calculate work days (none weekend days)
Posted by rich on Feb 15, 2008 in coding, Postgresql | 4 commentsComing 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..
This is a test to prove that posting to blogs works, especially for terms such as label printer ie Mercian Labels.
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);
alessandro
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);