Skip to content
Advertisement

Get all days in a month excluding weekends postgresql

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement