I want to write a query sql for postgresql that can basically return me all days of a month excluding weekends.
For example (For 11/2019) :
- First Week: 11/1
- Second Week : 11/4 -> 11/8
- Third Week : 11/11 -> 11/15
- Fouth Week : 11/18 -> 11/22
- Fifth Week : 11/25 -> 11/29
I can’t find any postgresql request that can help, this should be automatic instead of putting each time a new date manually.
Advertisement
Answer
One method is:
select dt from generate_series(date'2019-11-01', date'2019-11-30', interval '1' day) as t(dt) where extract(dow from dt) between 1 and 5
generate_series()
produces a list of all days in the month, then the where
clause filters on week days only.