Code for getting particular day of week number from month - Mailing list pgsql-general

Hi

For a particular sequence I needed to do (schedule 2nd monday in month for coming year) I created the following query

    select to_char(min(date::date) + interval '1 week','DD/MM/YYYY')  date

--gets first date for day of month (monday in this case) then adds week and finally formats it to desired date string

    from generate_series(
  '2018-12-01'::date,

--start date
  '2020-12-01'::date,

--end date
  '1 day'::interval
    ) date
   
where extract(dow from date) =1

--sets day of week
    GROUP BY (extract(year from date)*100)+extract(month from date)

--groups by month and year
ORDER BY cast(min(date) as date)

--sets order back to date

I couldn't see anything on google so thought I'd share it

Mike