MxBlog

How to determine date of first monday of the month using postgres

Short snippet to show you how to determine the first monday of every month next year using sql:

with first_day_of_month as (
  SELECT * FROM generate_series('2017-01-01 00:00'::timestamp,
                              '2017-12-31 12:00', '1 months') as firstday
) 
select fdom.firstday
,      extract(dow from fdom.firstday) as dow
,      to_char(fdom.firstday,'day') as dayname
,      fdom.firstday::date + (8 - extract(dow from fdom.firstday))::integer%7 as firstmonday
,      to_char(fdom.firstday::date + (8 - extract(dow from fdom.firstday))::integer%7,'day') as firstmonday_dayname
from   first_day_of_month as fdom
;

The result of this is:

firstday            |dow |dayname   |firstmonday |firstmonday_dayname |
--------------------|----|----------|------------|--------------------|
2017-01-01 00:00:00 |0.0 |sunday    |2017-01-02  |monday              |
2017-02-01 00:00:00 |3.0 |wednesday |2017-02-06  |monday              |
2017-03-01 00:00:00 |3.0 |wednesday |2017-03-06  |monday              |
2017-04-01 00:00:00 |6.0 |saturday  |2017-04-03  |monday              |
2017-05-01 00:00:00 |1.0 |monday    |2017-05-01  |monday              |
2017-06-01 00:00:00 |4.0 |thursday  |2017-06-05  |monday              |
2017-07-01 00:00:00 |6.0 |saturday  |2017-07-03  |monday              |
2017-08-01 00:00:00 |2.0 |tuesday   |2017-08-07  |monday              |
2017-09-01 00:00:00 |5.0 |friday    |2017-09-04  |monday              |
2017-10-01 00:00:00 |0.0 |sunday    |2017-10-02  |monday              |
2017-11-01 00:00:00 |3.0 |wednesday |2017-11-06  |monday              |
2017-12-01 00:00:00 |5.0 |friday    |2017-12-04  |monday              |