PostgreSQL date query from hell

Question

This query needs to return all columns from the price table for rows that match a specific list of dates. The requirements for the list of dates are between () in the pseudo query below.

At issue here is how to build the list of dates.

The requirement is beyond my current understanding of SQL, but hopefully I can learn from your responses.

-- Given two tables, price_tbl (column price_date) and holiday_tbl (column holiday_date):

select * from price_tbl where price_date in 
  ( 1. Last business  day of every Month from current_date - 30 months to 24 months later  -- (total of 24 months)
    2. Last business  day of every Week  from last date of '1.' to 24 weeks later          -- (total of 24 weeks)
    3. Every business day from last date of '2.' to current_date                           -- (variable number of days - approx 3 to 33 days possible)
  )
 order by price_date;

NB:

For '1.' and '2.' any business day that is in the holiday_tbl must be replaced by the closest preceding business day that is not in the holiday_tbl.
For '3.' any dates in the holiday_tbl are excluded.
It can be assumed that there will be no contiguous holidays that fill an entire week.

I have an Excel table with the date patterns as described above. FWIW, the formulas are listed here in case they may help:

--
-- Excel formulas that match the above:
--  
-- First Month    (in cell B3)     :  =IF(WEEKDAY(EOMONTH(TODAY(),-30),2)>5,WORKDAY(EOMONTH(TODAY(),-24),-1,'PubHol'!$B$5:$B$54),EOMONTH(TODAY(),-30))
-- Next 23 months (in cell B4:B26) :  =IF(WEEKDAY(EOMONTH($B3,1),2)>5,WORKDAY(EOMONTH($B3,1),-1,'PubHol'!$B$5:$B$54),EOMONTH($B3,1))
-- First Week     (in cell B27)    :  =IF(WEEKDAY($B26,2)=5,$B26+7,WORKDAY($B26+(6-WEEKDAY($B26,2)),-1,'PubHol'!$B$5:$B$54))
-- Next 23 weeks  (in cell B28:B50):  =WORKDAY($B27+9,-1,'PubHol'!$B$5:$B$54)
-- All following days              :  =WORKDAY($B50,1,'PublHol'!$B$5:$B$54)
--

Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Mar 07 '18 at 15:23

JR ibkr · Answer 1 · 2018-03-07T18:23:38.517

Use SQL query suggested on Postgresql query between date ranges. Most of the languages do have a date API. Use it to generate precise dates based on the supplied input.

Input(Week/day/month) -> Convert it to exact dates -> Create a function which takes this dates and fire the query.

Also, you can use, Postgres database's date/time feature
https://www.postgresql.org/docs/current/static/functions-datetime.html.

PostgreSQL date query from hell

1 Answers1