Filter out records in specified day of week in PostgreSQL

Question

I have inner query which is returning a specified date according to the date between condition. im successfully getting results but in outer query i want to check whether date is in the string i have provided ex-'Friday,Saturday'

SELECT * from 
    (
    SELECT id, to_char(markupdate, 'Day') as dayofweek 
    FROM test t 
    WHERE t.markupdate BETWEEN 'Thu Jul 11 00:00:00 IST 2019' AND 'Sat Jul 20 00:00:00 IST 2019'
    ) data
    WHERE data.dayofweek in ('Friday,Sunday');

Answer 1

You can use a regular expression to match your data.dayofweekto the Strings you are looking for (Friday, Sunday). You don't even need your subquery, something like that should also work:

 SELECT id, to_char(markupdate, 'Day') as dayofweek 
 FROM test t 
 WHERE t.markupdate BETWEEN '2019-07-11 00:00:00+0530' AND '2019-07-20 00:00:00+0530'
 AND dayofweek~'(Friday|Sunday)';

For more background infos check the Postgres docs.

Answer 2

SELECT id, to_char(markupdate, 'Day') as dayofweek 
FROM test t 
WHERE t.markupdate BETWEEN 'Thu Jul 11 00:00:00 IST 2019' AND 'Sat Jul 20 00:00:00 IST 2019' 
AND date_part('dow', markupdate) in (5,0);

Week dates in postgres return values as follows:

0 Sunday
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday

Instead of date_part('dow', markupdate) you can use the more standard SQL EXTRACT('dow' FROM markupdate), but dow is an extension, not part of the SQL standard.