title: PostgreSQL custom week number - first week containing Feb 1st
body: <p>I'm new to SQL functions and trying to create a calendar table that displays custom week numbers, with each week starting with Saturday and ending on Friday. The first week of each year always contains Feb.1st of that year. 
  For example, if the day of the week of Feb. 1st for a particular year is Tuesday, then the <em>first week</em> for that year is <em>from Jan. 29 to Feb. 4</em>.</p>
  
  <p>I've been struggling with this problem for a couple days and the only solution I can come up with is as follows:</p>
  
  <p>First, I created a calendar table with a column called "CustomizedWeekNo" to reflect the year cycle starting from the week containing Feb. 1st. But the first day of each week is Monday.</p>
  
  <pre><code>Create Table Calendar
  (CalendarDate Date, WeekNo smallInt, WeekDayNo text, CustomizedWeekNo smallInt)
  
  Create or Replace Function CustomizeWeekNumber() 
  Returns void
  as $$
  Declare beginDate Date :='2015-01-31'; endDate Date := '2017-01-27';
  Begin 
  While beginDate &lt;= endDate loop
  Insert Into Calendar (CalendarDate, WeekNo, WeekDayNo, CustomizedWeekNo)
  Select
  beginDate As CalendarDate    
  ,DATE_PART('week', beginDate::timestamp)::smallint As WeekNo
  ,(Case When DATE_PART('isodow', beginDate::timestamp)::smallint = 6
            Then 'Sat'
       When DATE_PART('isodow', beginDate::timestamp)::smallint = 7
            Then 'Sun'
       When DATE_PART('isodow', beginDate::timestamp)::smallint = 1
            Then 'Mon'
       When DATE_PART('isodow', beginDate::timestamp)::smallint = 2
            Then 'Tue'
       When DATE_PART('isodow', beginDate::timestamp)::smallint = 3
            Then 'Wed'
       When DATE_PART('isodow', beginDate::timestamp)::smallint = 4
            Then 'Thur'
       Else 'Fri'
       End) As WeekDayNo;
  ,(Case When beginDate &lt; '2016-01-04' 
            Then DATE_PART('week', beginDate::timestamp)::smallint - 5 
       When beginDate &gt;= '2016-01-04' and beginDate &lt; '2016-01-30' 
            Then (date_part('week', '2016-01-03'::timestamp)::smallint - 5 + date_part('week', beginDate::timestamp)::smallint)
       When beginDate &gt;= '2016-01-30' and beginDate &lt; '2017-01-02'
            Then date_part('week', beginDate::timestamp)::smallint - 4
       Else
            date_part('week', '2017-01-01'::timestamp)::smallint - 4 + date_part('week', beginDate::timestamp)::smallint
       End) As CustomizedWeekNo; 
  Select (beginDate + interval'1 day') into beginDate;
  End loop;
  End; $$
  language plpgsql;  
  
  # Run the function
  select CustomizeWeekNumber()
  </code></pre>
  
  <p>Next, I update the "CustomizedWeekNo" column</p>
  
  <pre><code>-- My customized week starts from every Saturday and ends on every Friday
   update calendar
   set CustomizedWeekNo = CustomizedWeekNo + 1 
   where WeekDayNo in ('Sat', 'Sun');
  </code></pre>
  
  <p>Lastly, I create another function to return the information I need. I also reformat the value of the "CustomizedWeekNo" to include the specific year.</p>
  
  <pre><code>create or replace function update_CustomizedWeek(date, date)
  returns table(Calendar_Date Date, Week_No int, WeekDay_No text, Customized_Week_No int)
  as $$
  begin
    return query
    select t.CalendarDate, t.WeekNo, t.WeekDayNo, 
      case when t.CustomizedWeekNo &lt;= 9 
           then (date_part('year', t.CalendarDate::timestamp)::text||'0'||t.CustomizedWeekNo::text)::int 
      else (date_part('year', t.CalendarDate::timestamp)::text||t.CustomizedWeekNo::text)::int 
      end
    from Calendar t 
    where t.CalendarDate &gt;= $1 and t.CalendarDate &lt;= $2
    order by t.CalendarDate;
  end; $$
  language plpgsql;
  
  --Example
  select * from update_CustomizedWeek('2015-01-30', '2015-02-10')
  </code></pre>
  
  <p>The final result will look like:</p>
  
  <pre><code>Calendar_Date | Week_No | WeekDay_No | Customized_Week_No
  ------------- | ------- | ---------- | -------------------
  2015-01-31    |   5     |   Sat      |  201501
  2015-02-01    |   5     |   Sun      |  201501
  2015-02-02    |   6     |   Mon      |  201501
  2015-02-03    |   6     |   Tue      |  201501
  2015-02-04    |   6     |   Wed      |  201501
  2015-02-05    |   6     |   Thur     |  201501
  2015-02-06    |   6     |   Fri      |  201501
  2015-02-07    |   6     |   Sat      |  201502
  2015-02-08    |   6     |   Sun      |  201502
  2015-02-09    |   7     |   Mon      |  201502
  2015-02-10    |   7     |   Tue      |  201502
  </code></pre>
  
  <p>As you can see, I used a lot of "hard coding" here. I would like to be able to generate a date range along with the customized week number for any year, not just 2016 or 2017. Any help is really appreciated. </p>