[SOLVED] Filter out records in specified day of week in PostgreSQL

Filter out records in specified day of week in PostgreSQL

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');

Solution

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.