Get week number, with weeks starting on Sunday, like Excel WEEKNUM

Question

In PostgreSQL (I'm on version 9.6.6), what's the simplest way to get the week number, starting on Sunday?

DATE_PART('week',x) returns:

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year. (doc)

Say my query is like:

WITH dates as (SELECT generate_series(timestamp '2014-01-01', 
                                      timestamp '2014-01-31', 
                                      interval  '1 day'
                                      )::date AS date
) 
SELECT 
    date, 
    TO_CHAR(date,'Day') AS dayname,
    DATE_PART('week',date) AS weekofyear
FROM dates

Returns:

date        dayname   weekofyear
--------------------------------
2014-01-01  Wednesday   1
2014-01-02  Thursday    1
2014-01-03  Friday      1
2014-01-04  Saturday    1
2014-01-05  Sunday      1 <- I want this to be 2
2014-01-06  Monday      2
2014-01-07  Tuesday     2
2014-01-08  Wednesday   2

So far I have tried:

SELECT 
    date, 
    TO_CHAR(date,'Day') AS dayname,
    DATE_PART('week',date) AS week_iso,
    DATE_PART('week',date + interval '1 day') AS week_alt
FROM dates

which won't quite work if the year begins on a Sunday.

Also, I want week 1 to contain January 1 of that year. So if January 1 is a Saturday, I want week 1 to be one day long (instead of being week 53 in the ISO style). This behavior is consistent with the Excel WEEKNUM function.

Answer 1

To get the week number of the year, with weeks starting on Sunday, we need to know how many Sundays between the first day of the year and the target date.

I adapted the solution here by @Erwin Brandstetter. This solution counts Sundays inclusive of the first day of the year and exclusive of the target date.

Then, because I want the first (partial) week to be week one (not zero), I need to add 1 unless the first day of the year is a Sunday (in which case it's already week one).

WITH dates as (SELECT generate_series(timestamp '2014-01-01', 
                                      timestamp '2014-01-31', 
                                      interval  '1 day'
                                      )::date AS date
) 
SELECT 
    date, 
    TO_CHAR(date,'Day') AS dayname,
    DATE_PART('week',date) AS week_iso,
    ((date - DATE_TRUNC('year',date)::date) + DATE_PART('isodow', DATE_TRUNC('year',date)) )::int / 7 
       + CASE WHEN DATE_PART('isodow', DATE_TRUNC('year',date)) = 7 THEN 0 ELSE 1 END  
         AS week_sundays

FROM dates

Returns

date        dayname   weekofyear   week_sundays
--------------------------------
2014-01-01  Wednesday   1   1
2014-01-02  Thursday    1   1
2014-01-03  Friday      1   1
2014-01-04  Saturday    1   1
2014-01-05  Sunday      1   2
2014-01-06  Monday      2   2
2014-01-07  Tuesday     2   2

To show how this works for years starting on Sunday:

2017-01-01  Sunday      52  1
2017-01-02  Monday      1   1
2017-01-03  Tuesday     1   1
2017-01-04  Wednesday   1   1
2017-01-05  Thursday    1   1
2017-01-06  Friday      1   1
2017-01-07  Saturday    1   1
2017-01-08  Sunday      1   2

Answer 2

The task is not as daunting as it first appears. It mainly requires finding the first Sun on or after the 1-Jan. That date becomes the last day of the first week. From there calculation of subsequent weeks is merely. a matter of addition. The other significant point is with week definition there will always be 53 week per year and the last day of the last week is 31-Dec. The following generates an annual calendar for this week definition.

create or replace function non_standard_cal(year_in integer)
  returns table (week_number integer, first_day_of_week date, last_day_of_week date)
  language sql immutable leakproof strict rows 53
  as $$
with recursive cal as
     (select 1 wk, d1 start_of_week, ds end_of_week, de stop_date
        from (select d1+substring( '0654321' 
                             , extract(dow from d1)::integer+1
                             , 1)::integer ds
                  , d1, de
               from ( select make_date (year_in, 1,1)      d1
                           , make_date (year_in+1, 1,1) -1 de
                    ) a
             ) b
      union all 
      select wk+1, end_of_week+1,  case when end_of_week+7 > stop_date
                                        then stop_date  
                                        else end_of_week+7
                                   end
             , stop_date
        from cal
       where wk < 53
     )                                         
select wk, start_of_week, end_of_week from cal; 
$$ ;  

As a general rule I avoid magic numbers, but sometimes they're useful; as in this case. In magic number (actually a string) '0654321' each digit represents the number of days needed to reach the first Mon on or after 1-Jan when indexed by the standard day numbering system (0-6 as Sun-Sat). The result is the Mon being the last day of the first week. That generatess the 1st row of the recursive CTE. The remaining rows just add the appropriate number days for each week until the 53 weeks have been generated.
The following shows the years needed to ensure each day of week gets it's turn to 1-Jan (yea some days duplicate). Run individual years to validate its calendar.

do $$
declare 
  cal record;
  yr_cal cursor (yr integer) for
        select * from non_standard_cal(2000+yr) limit 1;

begin 
  for yr in 18 .. 26
  loop 
       open yr_cal(yr);
       fetch yr_cal into cal;
       raise notice 'For Year: %, week: %, first_day: %, Last_day: %, First day is: %'
                    , 2000+yr
                    ,cal.week_number
                    ,cal.first_day_of_week
                    ,cal.last_day_of_week
                    ,to_char(cal.first_day_of_week, 'Day');
       close yr_cal;
  end loop;
 end; $$; 

Answer 3

Following may work - tested with two cases in mind:

WITH dates as (SELECT generate_series(timestamp '2014-01-01', 
                                      timestamp '2014-01-10', 
                                      interval  '1 day'
                                      )::date AS date
               union
               SELECT generate_series(timestamp '2017-01-01', 
                                      timestamp '2017-01-10', 
                                      interval  '1 day'
                                      )::date AS date
) 
, alt as (
SELECT 
    date, 
    TO_CHAR(date,'Day') AS dayname,
    DATE_PART('week',date) AS week_iso,
    DATE_PART('week',date + interval '1 day') AS week_alt
FROM dates
    )
select date, dayname, 
week_iso, week_alt, case when week_alt <> week_iso 
                           then week_alt
                           else week_iso end as expected_week
from alt
order by date

Output:

date        dayname   week_iso   week_alt expected_week
2014-01-01  Wednesday   1   1   1
2014-01-02  Thursday    1   1   1
2014-01-03  Friday      1   1   1
2014-01-04  Saturday    1   1   1
2014-01-05  Sunday      1   2   2
2014-01-06  Monday      2   2   2
2014-01-07  Tuesday     2   2   2
....
2017-01-01  Sunday      52  1   1
2017-01-02  Monday      1   1   1
2017-01-03  Tuesday     1   1   1
2017-01-04  Wednesday   1   1   1
2017-01-05  Thursday    1   1   1
2017-01-06  Friday      1   1   1
2017-01-07  Saturday    1   1   1
2017-01-08  Sunday      1   2   2

Answer 4

This query works perfectly replacing monday with sunday as the start of the week.

QUERY

 SELECT CASE WHEN EXTRACT(day from '2014-01-05'::date)=4 AND
 EXTRACT(month from '2014-01-05'::date)=1 THEN date_part('week',
 '2014-01-05'::date) ELSE date_part('week', '2014-01-05'::date + 1)
 END;

OUTPUT

  date_part
 -----------
          2 
(1 row)