On Tue, Dec 11, 2018 at 2:10 PM Mike Martin <redtux1@gmail.com> wrote:
> For a particular sequence I needed to do (schedule 2nd monday in month for coming year) I created the following
query
....
nice, but a little brute force.
Is this what you are trying to do:
$ select d::date as month_starts, to_char(date_trunc('week',d-'1
day'::interval)::date+14,'YYYY-MM-DD Day') as "2nd_monday" from
generate_series('2018-12-01'::date, '2020-12-01'::date,'1
month'::interval ) months(d);
Explanation:
generate_series for 1st day of each month.
1.- substract a day to get LAST day of previous month.
2.- truncate to week, which happily for us sends it to monday on my
locale ( YMMV ).
3.- Now you have LAST monday of PREVIOUS month, just go forward as
many weeks as needed.
If other DOW is needed, say wednesday, adjust substraction in previous
phase ( i.e., last wednesday of NOVEMBER is 2 days AFTER last MONDAY
before november 28 (two days BEFORE end of november ), If I'm doing
the math right get it right, so you would use something like:
date_trunc('week', -- this truncates to mondays so
d -- currrent month start.
-'1 day'::interval -- last month end
-'2 day'::interval -- diff from used day and the ones date_trunc returns.
)::date -- back to dates so we can use integer for lazy typers.
+2 -- restore the 2 days we took before,
+14 -- and add a couple of weeks.
This is the tricky part, as date_trunc rounds down you have to play a
bit with where it rounds.
And then, 2nd MONDAY of december is 14 days AFTER last monday of november.
You count from the end of the previous month because date_trunc goes
down, if you have a function "rounding dates up" it would be much
easier.
Results:
month_starts | 2nd_monday
--------------+----------------------
2018-12-01 | 2018-12-10 Monday
2019-01-01 | 2019-01-14 Monday
2019-02-01 | 2019-02-11 Monday
2019-03-01 | 2019-03-11 Monday
2019-04-01 | 2019-04-08 Monday
2019-05-01 | 2019-05-13 Monday
2019-06-01 | 2019-06-10 Monday
2019-07-01 | 2019-07-08 Monday
2019-08-01 | 2019-08-12 Monday
2019-09-01 | 2019-09-09 Monday
2019-10-01 | 2019-10-14 Monday
2019-11-01 | 2019-11-11 Monday
2019-12-01 | 2019-12-09 Monday
2020-01-01 | 2020-01-13 Monday
2020-02-01 | 2020-02-10 Monday
2020-03-01 | 2020-03-09 Monday
2020-04-01 | 2020-04-13 Monday
2020-05-01 | 2020-05-11 Monday
2020-06-01 | 2020-06-08 Monday
2020-07-01 | 2020-07-13 Monday
2020-08-01 | 2020-08-10 Monday
2020-09-01 | 2020-09-14 Monday
2020-10-01 | 2020-10-12 Monday
2020-11-01 | 2020-11-09 Monday
2020-12-01 | 2020-12-14 Monday
(25 rows)
Francisco Olarte.
