How can I use date_trunc in postgresql to round weekend days to the following weekday?

Question

SELECT DATE_TRUNC('day', trade_date + INTERVAL '1 DAY') from trade_table;

I have dates that are all on Monday through Friday, but when I date_trunc a date that is on Saturday (e.g. taking weekday plus 1), it is rounded to Saturday. I want it to be rounded to the following Monday rather than Saturday. How can I do that?

Answer 1

If I understand right you want to add one day and if the result is Saturday or Sunday add 2 or 1 more days, so the result is the next working day. Then extracting first the day of the week (dow) and using a CASE expression, will do:

select date_trunc('day', 
                  trade_date + ( case extract(dow from trade_date)
                                   when 5 THEN 3     -- when Friday, add 3
                                   when 6 THEN 2     -- when Saturday, add 2
                                   else 1            -- any other day, add 1
                                 end
                               ) * interval '1 day' )  
from trade_table ;