>>>>> "Mike" == Mike Martin <redtux1@xxxxxxxxx> 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)
