0

This year 2014 has:

Jan-4
Feb-4
Mar-5
Apr-4
May-4
Jun-5
Jul-4
Aug-4
Sep-5
Oct-4
Nov-4
Dec-5

How to calculate this for any given year?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Mark Jason Bagsic
  • 19
  • 1
  • 1
  • 1
  • 2
    possible duplicate of [PHP get number of week for month](http://stackoverflow.com/questions/5853380/php-get-number-of-week-for-month) – Sal00m Sep 04 '14 at 07:13
  • Why not just understand the _rule_ for numbering the weeks in a year: http://en.wikipedia.org/wiki/ISO_week_date#Weeks_per_year –  Sep 04 '14 at 07:26
  • 1
    Your question does not make sense. *The ISO standard does not define any association of weeks to months. A date is either expressed with a month and day-of-the-month, or with a week and day-of-the-week, **never a mix**.* http://en.wikipedia.org/wiki/ISO_week_date#Weeks_per_month – pozs Sep 04 '14 at 08:13
  • Sounds like a valid question to me. Just define more closely what "in" is supposed to mean `in How many weeks are in each month`. Also, you have "this year" twice in your sentence. Suspecting a mistake, please clarify. – Erwin Brandstetter Sep 04 '14 at 21:28

2 Answers2

1

There are multiple ways to define "weeks in a month" exactly. Assuming your count is defined (as your numbers indicate):

   How many Mondays lie in each month of the year?

You can generate it like that:

Simple:

SELECT EXTRACT(month FROM d) AS mon, COUNT(*) AS weeks
FROM   generate_series('2014-01-01'::date
                     , '2014-12-31'::date
                     , interval '1 day') d
WHERE  EXTRACT(isodow FROM d) = 1  -- only Mondays
GROUP  BY 1
ORDER  BY 1;

Fast:

SELECT EXTRACT(month FROM d) AS mon, COUNT(*) AS weeks
FROM   generate_series ('2014-01-01'::date  -- offset to first Monday
                      + (8 - EXTRACT(isodow FROM '2014-01-01'::date)::int)%7 
                      , '2014-12-31'::date
                      , interval '7 days') d
GROUP  BY 1
ORDER  BY 1;

Either way you get:

mon  weeks
1    4
2    4
3    5
4    4
5    4
6    5
7    4
8    4
9    5
10   4
11   4
12   5

Just replace 2014 with the year of interest in each query.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • The ISO standard isn't concerned with the number of Mondays in a month. (I'm not sure whether that's what the OP is trying to get at.) The first Monday in 2014 is actually 2013-12-30, as far as ISO weeks are concerned. – Mike Sherrill 'Cat Recall' Sep 04 '14 at 23:48
  • @Mike: The first Monday in the *ISO* year 2014 is 2013-12-30. The first Monday in the Gregorian year (which is the common meaning of "2014") is 2014-01-06. The "number of weeks in a month" isn't defined by ISO - or by the question. I reverse engineered the most likely meaning from the given numbers. – Erwin Brandstetter Sep 05 '14 at 01:30
0

Applying the ISO 8601 to a month as suggested here

select
    to_char(d, 'YYYY Mon') as "Month",
    case when
        extract(dow from d) in (2,3,4)
        and
        extract(day from (d + interval '1 month')::date - 1) + extract(dow from d) >= 33
        then 5
        else 4
    end as weeks
from generate_series(
    '2014-01-01'::date, '2014-12-31', '1 month'
) g (d)
;
  Month   | weeks 
----------+-------
 2014 Jan |     5
 2014 Feb |     4
 2014 Mar |     4
 2014 Apr |     4
 2014 May |     5
 2014 Jun |     4
 2014 Jul |     5
 2014 Aug |     4
 2014 Sep |     4
 2014 Oct |     5
 2014 Nov |     4
 2014 Dec |     4
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260