how to filter my database with only "Monday" queries?

Question

I am trying to extract only monday from timestamp (in time,date,month format) in my database (would do count on it after wards). I tried to convert my dates to string characters. I was able to get all days in text format.

select to_char (payment_date, 'dy') as days from payment;

however, when i try to add where in it, to filter days, it gives an error.

select to_char (payment_date, 'dy') as days 
from payment 
where days.payment_date = 'mon';

Answer 1

You might want something like this (all of the code below is available on the fiddle here):

Generate a table with all of the dates in the first half of 2022.

CREATE TABLE dat AS
SELECT the_day FROM GENERATE_SERIES 
  ('2022-01-01'::TIMESTAMPTZ, '2022-06-30'::TIMESTAMPTZ, '1 DAY') AS t(the_day);

and then run:

SELECT
  the_day::DATE,
  EXTRACT(ISODOW FROM the_day),
   to_char(the_day, 'Day')
FROM
  dat
WHERE 
    EXTRACT(ISODOW FROM the_day) = 1;

Result:

the_day     extract     to_char
2022-01-03  1   Monday   
2022-01-10  1   Monday   
2022-01-17  1   Monday   
2022-01-24  1   Monday   
2022-01-31  1   Monday   
2022-02-07  1   Monday   
2022-02-14  1   Monday   
2022-02-21  1   Monday   
2022-02-28  1   Monday   
2022-03-07  1   Monday  
...
... snipped for brevity
...

or similarly:

SELECT
  the_day::DATE,
  EXTRACT(ISODOW FROM the_day),
  to_char(the_day, 'DAY')
FROM
  dat
WHERE
  to_char(the_day, 'DAY') = 'WEDNESDAY';

Result:

the_day     extract     to_char
2022-01-05  3   WEDNESDAY
2022-01-12  3   WEDNESDAY
2022-01-19  3   WEDNESDAY
2022-01-26  3   WEDNESDAY
2022-02-02  3   WEDNESDAY
2022-02-09  3   WEDNESDAY
...
... snipped for brevity
...