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

>>>>> "Mike" == Mike Martin <redtux1@gmail.com> writes:

 Mike> Hi

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

That doesn't look like the best way - you're generating and discarding a
lot of rows.

"second monday in month X" can be expressed as follows:

"second monday in X" = "(first monday in X) + 1 week"

"first monday in X"
  = "first day of X + N days, where N is (dow(Monday) - dow(1st))
     reduced to 0..6 mod 7"

i.e. if the month starts on Monday, N=0
  .. on Tuesday, N = 6   (1 - 2 == 6 mod 7)
  .. on Wednesday, N = 5  etc.

So:

select to_char(d, 'Day DD/MM/YYYY')
  from (select month
               + interval '1 week'
               + ((1 - extract(dow from month)::integer + 7) % 7)
                 * interval '1 day'
               as d
          from generate_series(timestamp '2018-12-01',
                               timestamp '2020-12-01',
                               interval '1 month') month) s;

-- 
Andrew (irc:RhodiumToad)