Code for getting particular day of week number from month

From: Mike Martin <redtux1(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Code for getting particular day of week number from month
Date: 2018-12-11 13:09:46
Message-ID: CAOwYNKa9zs+2ugvGfKUKjiu8JAm11SNx_O5XQVJV2x_RdFxg=g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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,
--start date
'2020-12-01'::date,
--end date
'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)
--groups by month and year
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Withers 2018-12-11 14:00:33 finding out what's generating WALs
Previous Message Olga Vingurt 2018-12-11 11:07:49 Re: pg_restore fails due to foreign key violation