Code for getting particular day of week number from month

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.

"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)