To get weekday between two date in Postgresql

Question

I need to get all week days in a given time interval.

In postgresql, there are dow and isodow

By mixing them together, may I write a function to retrieve weekdays?

Answer 1

As far as I understand you need to extract all Monday..Fridays between two dates. Here is an illustration with 2020-11-30 as the beginning of the interval and 2020-12-12 as the end of it.

select d 
 from generate_series('2020-11-30'::date, '2020-12-12'::date, '1 day'::interval) t(d) 
 where extract(isodow from d) between 1 and 5;

Answer 2

demo:db<>fiddle

SELECT
    generated_date,
    to_char(generated_date, 'Day'),             -- 1
    EXTRACT(isodow FROM generated_date),        -- 2
    EXTRACT(dow FROM generated_date)            -- 3
FROM
    generate_series('2020-11-01'::date, '2020-11-10'::date, interval '1 day') AS generated_date

1. Returns the name for the weekday
2. Returns the number of the weekday (Monday = 1, Sunday = 7)
3. Returns the number of the weekday (Sunday = 0, Saturday = 6)

---

Edit:

If you want to get the days without weekend, you can filter by the dow/isodow values, e.g.:

SELECT
    generated_date::date
FROM
    generate_series('2020-11-01'::date, '2020-11-10'::date, interval '1 day') AS generated_date
WHERE 
    EXTRACT(isodow FROM generated_date) < 6