Re: Code for getting particular day of week number from month

>>>>> "Mike" == Mike Martin <redtux1(at)gmail(dot)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)