Query for computing yearweek

Question

I need to a help at query in such a way that in yearweek,in a year there are 52 weeks

2015 has 52 weeks but 2016 yearweek should start as 201601 that is the first week of year 2016, not as a continuation of 2015's 53rd week.

2015 jan 1--> 201501(1st week of 2015) likewise
2016 jan 1 --> 201601(1st week of 2016) it shouldnt be 201553(53rd week of 2015)

Answer 1

Postgres supports two types of "week" calculations. The ISO standard (which is what you not want) and a week numbering where the first week starts on the first day of the year.

These two can be obtained using the to_char() function (

select to_char(date '2016-01-01', 'WW') as week_january, 
       to_char(date '2015-12-31', 'WW') as week_december,
       to_char(date '2016-01-01', 'IW') as iso_week_january, 
       to_char(date '2016-12-31', 'IW') as iso_week_december

The above returns the following:

week_january | week_december | iso_week_january | iso_week_december
-------------+---------------+------------------+------------------
01           | 53            | 53               | 52               

If you need that as a real integer rather than a string, just cast the result to integer, e.g: to_char(date '2016-01-01', 'WW')::integer

The format for to_char() is explained in the manual:
http://www.postgresql.org/docs/9.5/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

---

As a side note: for the ISO week to work properly you would need to combine this with the ISO yearweek as well: to_char(date '2016-01-01', 'IYYY') will return 2015