CODEWITHSUNDEEP
postgresqldatetimepostgresql-9.3
christinang89
christinang89

Reputation: 101

How to find the number of weeks in that month given month of the year?

Given that I have month and year in 'YYYY-MM' format, how do I find the number of weeks in that month?

e.g. 2014-07 has 5 weeks.

I think the idea is to find the last date of the month and then finding the week of the month of that date. The following works if I put in a proper date, but I'm not sure how to reconstruct a date/timestamp given that my starting point is YYYY-MM.

SELECT TO_CHAR(DATE_TRUNC('month', timestamp '2014-07-31') 
    + INTERVAL '1 month' - INTERVAL '1 day', 'w' )

Thank you!

Upvotes: 1

Views: 2183

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324511

Well, you need to define "weeks". Is a calendar week that's partially within a month a "week"? Do you want complete 7-day periods? What? Do weeks begin on Sunday or Monday?

Do you want ISO weeks? Weeks according to some local financial rules? something else?

You must clearly define the problem before you can solve it. Often simply defining it helps you solve it.

Given date d of format YYYY-MM and data type text, this makes the first day of the month:

to_date(d, 'YYYY-MM');

then to get the last day, find the next month and subtract a day:

(to_date(d, 'YYYY-MM') + INTERVAL '1' MONTH) - INTERVAL '1' DAY

e.g.

regress=> SELECT (to_date('2014-07', 'YYYY-MM') + INTERVAL '1' MONTH) - INTERVAL '1' DAY;
      ?column?       
---------------------
 2014-07-31 00:00:00
(1 row)

From there you can do what you need to identify the weeks according to the definition appropriate for your application, e.g.:

regress=> SELECT extract(week from (to_date('2014-07', 'YYYY-MM') + INTERVAL '1' MONTH) - INTERVAL '1' DAY) - extract(week from to_date('2014-07', 'YYYY-MM'));
 ?column? 
----------
        4
(1 row)

according to one definition of "number of weeks".

Upvotes: 1

Related Questions