Thread: Code for getting particular day of week number from month
Hi
For a particular sequence I needed to do (schedule 2nd monday in month for coming year) I created the following query
select to_char(min(date::date) + interval '1 week','DD/MM/YYYY') date
--gets first date for day of month (monday in this case) then adds week and finally formats it to desired date string
from generate_series(
'2018-12-01'::date,
'2018-12-01'::date,
--start date
'2020-12-01'::date,
'2020-12-01'::date,
--end date
'1 day'::interval
) date
where extract(dow from date) =1
'1 day'::interval
) date
where extract(dow from date) =1
--sets day of week
GROUP BY (extract(year from date)*100)+extract(month from date)
GROUP BY (extract(year from date)*100)+extract(month from date)
--groups by month and year
ORDER BY cast(min(date) as date)
ORDER BY cast(min(date) as date)
--sets order back to date
I couldn't see anything on google so thought I'd share it
Mike
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)