Run query with different filters based on day of week PostgreSQL

Question

Trying to run the same view with different date filters depending on which day I'm running it. If I run the query on a Fri, Sat, Sun, or Mon then the dates should be filtered on Tues-Thurs. If I run the query on Tues, Wed, Thurs then the dates should be filtered on Fri-Mon. Those are the only two scenarios. I'm using PostgreSQL.

The view itself is pretty simple otherwise:

select * from tbl_1
where date between ____ and _____

I do have a dates table that I can join to. I've tried something like:

(select date
,case when day_of_week_iso IN(2, 3, 4) THEN 1 ElSE 4 END as "day"
from tbl.date
where date = current_date
) dates

klin klin · Accepted Answer · 2016-05-17T20:15:45

Use extract(dow from current_date) which gives current day of week (Sunday is 0), see Date/Time Functions and Operators.

select *
from tbl_1
where case extract(dow from current_date)
    when 2 then date between current_date- 4 and current_date- 1
    when 3 then date between current_date- 5 and current_date- 2
    when 4 then date between current_date- 6 and current_date- 3
    when 5 then date between current_date- 3 and current_date- 1
    when 6 then date between current_date- 4 and current_date- 2
    when 0 then date between current_date- 5 and current_date- 3
    when 1 then date between current_date- 6 and current_date- 4
    end;

You could use your own function(s) to shorten the code, but the use of case with all cases is simpler and probably faster.

Run query with different filters based on day of week PostgreSQL

1 Answers