How can I get the previous weekday in PostgreSQL?

Question

I'm new to SQL and was just wondering how to get the previous weekday in PostgreSQL. I have select (current_date - 1) to get yesterday's date but I need this to exclude weekends. So for example, if today is Monday then I would expect this query to get Friday's date.

Answer 1

This isn't fancy, but it's what I use:

select case extract('dow' from current_date) 
         when 0 then current_date - 2 
         when 1 then current_date - 3 
         else current_date - 1 
       end;

┌────────────┐
│    case    │
├────────────┤
│ 2020-09-21 │
└────────────┘
(1 row)

Answer 2

You can use TO_CHAR() function with day abbreviation within a conditional statement :

SELECT CASE WHEN TO_CHAR(current_date-1, 'dy') = 'sun' THEN
                 current_date-3
            WHEN TO_CHAR(current_date-1, 'dy') = 'sat' THEN
                 current_date-2
            ELSE
                 current_date-1
             END
  FROM tab